10 Replies Latest reply: Feb 13, 2014 7:51 AM by user2495026 RSS

How to

user2495026 Newbie
Currently Being Moderated

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 Distinct

       Col1

      ,clo2

      ,col3

From

  Table_A  src1,

  (

   Select ... From Table_B

   UNION ALL

   Select ...  From Table_C

  )        Src2

WHERE Src1.Col1 = Src2.Col1

;

  • 1. Re: How to
    49f2b39b-c242-46ff-8aed-24acef91b31d Newbie
    Currently Being Moderated

    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.

     

    -Richard

  • 2. Re: How to
    user2495026 Newbie
    Currently Being Moderated

    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

    RR

  • 3. Re: How to
    FPonte Explorer
    Currently Being Moderated

    Hi.

     

    I deleted my suggestion because I missed the "Single Interface Requisition" in your first post.

     

    Sorry.

  • 4. Re: How to
    49f2b39b-c242-46ff-8aed-24acef91b31d Newbie
    Currently Being Moderated

    RR,

     

    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.

  • 5. Re: How to
    Shiv_Kumar Journeyer
    Currently Being Moderated

    Hi,

     

    You can perform here two steps in your mapping:-

     

    1. Firstly perform UNION operation and load the data into DATASET component of ODI.
    2. Then use the JOIN component of ODI between this DATASET and SOURCE2.

    After that you can load the final data into TARGET.

  • 6. Re: How to
    user2495026 Newbie
    Currently Being Moderated

    Hi Richard,

    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.

  • 7. Re: How to
    user2495026 Newbie
    Currently Being Moderated

    Thank you Shiv Kumar. I am using 11 g and not sure, how to load the data into DATASET component of ODI.

    I was wondering if you could share the screenshots or Steps to achieve this.

    Thanks RR

  • 8. Re: How to
    Shiv_Kumar Journeyer
    Currently Being Moderated

    To create a dataset in a mapping, drag a dataset from the component palette into the logical diagram and then simply drag and drop the required table columns into that dataset.

  • 9. Re: How to
    user2495026 Newbie
    Currently Being Moderated

    Thank you.


    I am looking to implement the requirement in 11g. I believe the component palette is in 12c and could not be implemented in 11g.


    Thanks

    RR

  • 10. Re: How to
    user2495026 Newbie
    Currently Being Moderated

    Thank you Richard.

     

    Is the flow Diagram is from ODI 12C ? 

     

    Now, I am looking to implement this with 11g temp target data store and then temp interface (use that as input as I could not do the similar steps as in your flow diagram) in 11g

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points