This post is about how to do a simple Minus query in ODI without creating any view or other process .
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 .
comparing the Data between the minus query and odi interface result reveals that they are similar.
Lets look at another example where the table columns are mismatched.
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)
Leave a reply →