Question On lookup

[rajuviv]

Hi ,

I am new to datastage and have a very basic question which i was able 2 achieve very easily using informatica.
I created a server job with a lookup to a country table using oracle stage has reference and in the reject file contraint i gave "LnkSrcTrf.country_id <> LnkLkpCountryMast.COUNTRY_ID" i.e when the source country_id is not present in the lookup country table go the reject file.
No records are loaded to the reject file even tho the source has different country id's not present in the country table and i am really confused as to why this is not working.

Plz let me know if my understanding abt the lookup strategy used is proper and also how to achieve the above reqiurement.

Thanks,
Vr

[chulett]

If I am following how your job is setup, your problem is more than likely due to a lack of NULL handling. One important thing to recognize is the fact that, when a lookup fails, the values in the lookup record are NULL. This is assuming you have defined a specific key (like country_id) in your OCI stage and are linking it with the contry_id coming in from your data source and are not doing something unusual via custom sql.

In your job, when the lookup fails it is true that the input country_id is "not equal" to the lookup country_id, but due to the fact that one of the values is null, the comparision will always fail - the result is "unknown".

In order to check the success or failure of a lookup, you can do one of two things:

The "old way" is to check the key value in the lookup record for a NULL. You know, the one you populated from your input stream? If you put something in there, do the lookup and it comes back null, then the lookup failed. Your reject constraint could simply say:

Code: Select all

IsNull(LnkLkpCountryMast.COUNTRY_ID)

The "new way" is to check the status of the lookup. This would make your constraint look like this:

Code: Select all

LnkLkpCountryMast.NOTFOUND

These same techniques can be used in column derivations.