ODIExperts.com

The blog for Oracle Data Integrator ( ODI )

MINUS IN ODI

This post is about how to do a simple Minus query in ODI without creating any view or other process .

 image

Join the two source  and apply a LEFT OUTER JOIN  and in the join query box delete the condition and write the query in this form namely.

NOT EXISTS ( SELECT ‘X’ FROM  SCHEMA_NAME.SOURCE_1 ( TABLE TO BE MINUS WITH ) WHERE JOINING CONDITION.

So for my Example it is 

NOT EXISTS ( SELECT ‘X’ FROM ODI_TEMP.SOURCE_1 WHERE SOURCE_2.EMP_ID=SOURCE_1.EMP_ID)

Also enable Distinct Rows option as there a cross join occurs and thus getting the right record .

image

comparing the Data between the minus query and odi interface result reveals that they are similar.

image

image

Lets look at another example where the table columns are mismatched.

image

Similarly LEFT OUTER JOIN  on the Source with to be compared and writing the condition and also enabling the distinct records option.

NOT EXITS( SELECT ‘X’ FROM ODI_TEMP.DUP_CHANNELS ( TO BE MINUS WITH) WHERE CHANNELS.CHANNEL_ID=DUP_CHANNELS.CHANNEL_ID( JOINING CONDITION)

 

image

image

One Comment

Leave a Reply

Required fields are marked *.