This discussion is archived
8 Replies Latest reply: Oct 10, 2013 11:54 PM by ABC_123 RSS

How to use Union, Union all, MINIUS in ODI

595649 Newbie
Currently Being Moderated
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 Explorer
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated

    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 Guru
    Currently Being Moderated

    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 Newbie
    Currently Being Moderated

    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.