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 .
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:
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 ...
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
FROM Table2 B
WHERE A.Col1 = B.Col1
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.
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.
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.