Hello All, I am looking for some assistance I am having trouble trying to merge a table into another, I have searched multiple different forums and scripts I just don't know where I am going wrong if at all I would like to be as simple as putting the exact database table into a named query if not I am open to other suggestions
You cannot do this in a single binding. JDBC doesn't have any mechanism to join across different connections. (But some databases can make their own, "foreign", connection, and do this for you.)
If you cannot make one of the databases connect to the other on your behalf, you will need two bindings, and join them yourself.
A script to do the join is a common approach, but I recommend my Integration Toolkit's expression functions for this task.
Can you please provide an example of how the crossjoin() or innerjoin() function works
This is more or less what I have
All I need is to join on the reason_id I really just need the other database to get the classification is there an easy way to do that
From the documentation...
innerJoin(datasetLeft, datasetRight, leftKeyExpr, rightKeyExpr [, ...])
innerJoin(
pathToDSBinding1, pathToDSBinding2,
"Reason_id", "Reason_id"
)
What Daniel said.
The binding that gets the classifications needs to get all of them that can be in the downtime table.
And this is going to be in a script correct
No, an expression binding, not a script. A script would be the alternative method, but less performant.
Note: you must install Phil's Integration Toolkit module for the innerJoin expression to be available for use.
where do I go to install Phil's Integration Toolkit module or find the module
Follow what I linked. Scroll to the bottom to find the latest. Or go through the IA Module Showcase.
I believe I am almost there here is what I have:
as you can see there are only 3 rows and columns in my original dataset I need to join them so I have 4 columns in total the classification class is there a for loop I can use to do this or some simple way more or less ( if id = 1 new col val (class) = 1)
The second table holds all the classes that I would need
It looks like your table of classifications has repeat reason IDs. There's no other way for a left join on that column to blow up the number of rows.
1 Like
I believe what is happening my 3x3 dataset is encompassing a where clause I just need the join to be the same reason_id and class as you can see it doing 3 times per reason_id and class
I see what's happening. You put just the column names as your join conditions. You actually need to extract the values with it(), like so:
leftJoin(
{value},
{[default]New Tag},
it()['Reason_id'],
it()['Reason_id']
)
Wrap that inside a columnRearrange() to drop any undesired columns.
2 Likes
Thank you so much that worked! the last thing I need help is there an easy way to group by class and add the total_time.
Uhm, groupBy(), perhaps? (Probably needs to be a separate result, not more rows.)
The Groupby() didn't work like I hoped any other suggesions