10 Replies Latest reply: Dec 19, 2012 5:52 AM by aetl RSS

    ODI 10g Union,Union All,Minus

    aetl
      Hello,

      I have to use union,union all or minus set on ODI 10.But it doesn't have uninon,union all minus data set like in ODİ 11.How can ı use union statement in odi 10 g?For example I have to union 6 table in odi 10g.How it can be ?

      Thnx a kot
        • 1. Re: ODI 10g Union,Union All,Minus
          JeromeFr
          It's not possible within 10g.


          There is two solutions to achieve your requirements :
          - Duplicate the interface 6 times and load only one table in each. You should only insert new rows (not existing ones) if you want Union and not Union all.
          - You can define a view making the union in your database.

          I would definitely go for the second one. With the first one, you have code duplication which is not good. You'll have to maintain 6 interfaces.

          Hope it helps.

          Regards,
          JeromeFr
          • 2. Re: ODI 10g Union,Union All,Minus
            aetl
            Thanks for reply
            Best regards
            • 3. Re: ODI 10g Union,Union All,Minus
              JeromeFr

              You're welcome!

              • 4. Re: ODI 10g Union,Union All,Minus
                aetl
                Best Regards

                Edited by: aetl on 15.Nis.2013 23:56
                • 5. Re: ODI 10g Union,Union All,Minus
                  JeromeFr
                  You can try this :
                  Create an interface and drag only the datastore that are not used in these 3 subselect.

                  Then drag a column from ra to the canvas to create a filter. Edit it so it looks like this :
                  (ra.row_id in (select pk_id from <%=odiRef.getInfo("SRC_SCHEMA")%>.dl_sbl_s_asset) or p.row_id in (select pk_id from <%=odiRef.getInfo("SRC_SCHEMA")%>.dl_sbl_s_prod_int) or a.row_id in (select pk_id from <%=odiRef.getInfo("SRC_SCHEMA")%>.dl_sbl_s_asset))
                  You only have to change ra, p and a by the aliases of your datastores.



                  Your generated SQL code should be similar to your original SQL script.
                  • 6. Re: ODI 10g Union,Union All,Minus
                    aetl
                    Thx a lot Jerome .it works

                    I have one more question:

                    This select captured changed datas.I will delete dataas which returns from this select.And then I will insert new datas.

                    I added to IKM COntrol append a new step like this:


                    delete from <%=odiRef.getTable("L","TARG_NAME","A")%> T WHERE (<%=odiRef.getTargetColList("", "t.[COL_NAME]", ", ", "\n", "UK")%>) in
                    (
                    SELECT PK_ID
                    FROM <%=odiRef.getFrom()%>
                    WHERE (1=1)
                    <%=snpRef.getJoin()%>
                    <%=snpRef.getFilter()%>
                    <%=snpRef.getJrnFilter()%>
                    <%=snpRef.getGrpBy()%>
                    <%=snpRef.getHaving()%>
                    )
                    /*commit*/

                    I selected "UK" from target.

                    But I dont know that it works true.I selected PK_ID from source but which PK_ID Will I select?When targets UK is equal to source UK(PK_ID) then these datas will be deleted.Changed datas will be inserted.How can I modify this step?İs there any solution to do this step?Can I get dynamicly PK_ID?

                    Could you help me ?

                    Best Regards

                    Thanks
                    • 7. Re: ODI 10g Union,Union All,Minus
                      aetl
                      Can anyone help me ?

                      Edited by: aetl on 17.Ara.2012 12:06
                      • 8. Re: ODI 10g Union,Union All,Minus
                        aetl
                        regards

                        Edited by: aetl on 15.Nis.2013 23:57
                        • 9. Re: ODI 10g Union,Union All,Minus
                          JeromeFr
                          I already had this problem.

                          ODI is adding a ( + ) on all your filters. I found 2 workarounds, but there is maybe a better solution :

                          1. Make the outer join by yourself by adding the ( + ) in your join condition (and thus by NOT checking the outer join checkbox). Drawback : You see it as an inner join on your canvas.
                          2. Use ordered-join. Check the box at the bottom of your join definition and give it a number. It will use the new ANSI syntax.
                          • 10. Re: ODI 10g Union,Union All,Minus
                            aetl
                            Hi Jerome

                            I resolved this problem like this:
                            I created new interface same as source not used subselect
                            I added to target columnt which is used in subselect.For example I added to target s_org_ext of row_id like so_row_id
                            row_id of s_asset-->sa_row_id
                            row_id of s_asset_xa-->xa_row_id
                            row_id of s_asset_xa2-->xa2_row_id
                            ..
                            ETC

                            I created Second interface.And I used this filter there.İt worked

                            Thank your helping

                            Regards.