I was wondering if there is a easy way to implement the following in a single interface in ODI 11g.
Here I know how to implement the UNION in ODI that writes to Target. However, What I am looking for is to take the RESULTSET (Src2) of the UNION in the same interface and JOIN with Another Source(Src1) AND then write the Resultset of that into target table (Table_insert)
I would appreciate any suggestions/implementations here.
Insert into Table_insert (col1,col2,col3)
Select ... From Table_B
Select ... From Table_C
WHERE Src1.Col1 = Src2.Col1
If I understand you correctly, you should be able to take the output of your union and src1 into a join with the join condition equal to your where clause. Then take the output of the join into your target table. I'm unable to attach images here, so here is an imgur link to a screenshot of a quick mapping that should do what you're looking for. If this isn't what you want of you have more questions, feel free to ask.
Thank you Richard for correct interpretation and answering with flow diagram quickly. This is exactly what I was looking for. If you could, It would be helpful to know how you connected the result set of the UNION to the SRC1 using Join. (meaning the How do i Join the Src1 and union.resultset) .
Also After the Final Join (union.resultset x Src1) I need to select only distinct rows of col1,col2,col3 ( group by col1,col2,col3) before loading the target.
Thank you so much
I'll preface this by saying that I am working on ODI version 12C. If you're still using version 11 the process will be different.
In order to join the output of your set operation and another table drag a join operator onto your mapping. Next, click on your join to highlight it and bring up its properties pane. In the connector points area of the properties pane, set your union as one input, your SRC3 table as the other input, and your target table as the output.
Now you need to define the join condition. If you highlight your join and look in the condition area of the properties pane, you should see a box labeled Join Condition. You can free type in this box to define the join condition, or you can drag columns from your mapping directly into this box to make sure you get the name spot on. In my last post, you should see the join condition box at the bottom of the screenshot I posted.
Lastly, you need to specify what the target table's columns are. The easiest way to do this it to drag the column you want from one of your join's inputs into the column you want in the target. This is assuming you're not trying to do anything more than a simple select.
I also just noticed that your example code listed a select distinct. You can achieve this by placing a distinct operator between your join and your target and connecting it all up.
Again, Thank you for your suggestions. As you mentioned the steps looks to be different in 11g.
Yes I am using 11g, When I create a set-operation Table_B UNION ALL with Table_C then where will be the resultset(Src2) to JOIN to Table_A src1.
I tried doing the Join in Table_A Src1 but not sure how to get the output of the union as one input to the join. When I create a set-operation of union , I will have two source panes. One for Table_B and the other Source Pane for Table_C.
Question: 1. Where (which Source pane) I should drag the table Table_A src1.
2. Where is the result of the union to join to table_a (& Which source Pane I should use for this final join)
Appreciated any help.