8 Replies Latest reply: Oct 11, 2013 1:54 AM by ABC_123 RSS

    How to use Union, Union all, MINIUS in ODI

    595649
      Hi,

      I have the following scenorio. I wnat to do in the ODI mappings. I do not have goo documentation to go through. Can any one help me.

      Thee are two tables A, B

      B has values which are not present in A. I need to do Union for the missed records.

      I am doing B minus A , and unioining with A. can any one help me how to do this in ODI. I have union and Minus in ODI expressions.

      VB
        • 1. Re: How to use Union, Union all, MINIUS in ODI
          Bouch
          You can do that directly wwith ODI ;(

          You don't need to do a Minus, an UNION is enought

          Create a view as
          select * from B
          union
          select * from A

          And reverse this view in your model and use it as the source of your interface.
          • 2. Re: How to use Union, Union all, MINIUS in ODI
            595649
            Hi Lyon ,

            The Scenorio here is both the tables have got few records which are identical for few columns. I am interested in rows which are not present in the Table A and present in Table B. Both the tables have diferent table structure but the required columns are present in both tables.

            You are asking me to create VIEW in Data base and reverse it? I can do that . But I want to do UNION or MINUS in interface. How to use two tables union in interface. I have to drag sources in to source area and create view using UNIon/ MINUS? in to target area. how to do that ? Please explain me .
            • 3. Re: How to use Union, Union all, MINIUS in ODI
              424400
              If you are looking for a UNION or MINUS operation in your ETL, maybe ODI isn't the right tool for you in this case. With another Oracle tool, called Oracle Warehouse Builder -or OWB-, you'll have more ETL-design-options in your interface (which is called a 'mapping' in OWB).
              Have a look at:
              http://www.oracle.com/technology/products/warehouse/htdocs/datasheet10g.htm

              If you want to use ODI, you have to use database-views, or maybe, write your own KM to handle with your issue.

              Good luck ...
              • 4. Re: How to use Union, Union all, MINIUS in ODI
                587939
                Hi there,

                For Set operations in ODI, I'll suggest two approach;

                1) You can use ODI procedures such that the source technology has the complex SQL query with UNION / MINUS...(treat it like an inline view). Then in the Target technology use your insert statement.
                This helps you loading data in a multi technolgy environment as well.

                2) In your case mention above and if you insist to use ODI interface, then you can always simulate the 'MINUS' operation using 'NOT EXIST' clasuse in the filter.

                Use it as below;

                SELECT Col1, Col2
                FROM Table1 A
                WHERE NOT EXISTS
                (
                SELECT 1
                FROM Table2 B
                WHERE A.Col1 = B.Col1
                )

                Regards,
                Karthik
                • 5. Re: How to use Union, Union all, MINIUS in ODI
                  595649
                  Hi Karthik,

                  Thanks. I see Minus and Union in expression editor. in what scenorios we can use them. Can you provide me with an example using union , minus in expression editor in a interface.
                  • 6. Re: How to use Union, Union all, MINIUS in ODI
                    user570489

                    Hello,

                     

                    ODI can't do union/union all / minus etc. by default. As written above you must use views or you must write a KM. One of my friend has written a knowledge module for union and union all. You can look over his KM and download it.

                     

                    http://ozukun.blogspot.com/2013/07/odi-knowledge-module-for-union-unionall.html

                     

                    Best Regards,

                    Serhat ALTINTAŞ

                    http://www.serhataltintas.com

                    • 7. Re: How to use Union, Union all, MINIUS in ODI
                      SH

                      ODI  11.x does support set operations directly despite what the other posts have stated. In your scenario in your interface you would simply create 2 datasets one containing source Table A and the second containing source table B. When you add the second dataset simply specify UNION as the operator between the 2 datasets. Simple.

                      • 8. Re: How to use Union, Union all, MINIUS in ODI
                        ABC_123

                        Hi  sh

                         

                        Well this KM basically developed for ODI 10g ( which we dont have dataset) , this is just a version for 11g , well like everybody i know 11g supports dataset operation.This basically not for only UNION also MINUS and UNION_ALL operations.Also give an example to end-users how to create sub_selects statements too. Plus i guess you dont read full article my example  has 3 sources into one target table.