Lookup Problem

Post questions here relative to DataStage Enterprise/PX Edition for such areas as Parallel job design, Parallel datasets, BuildOps, Wrappers, etc.

Moderators: chulett, rschirm, roy

Post Reply
chou
Participant
Posts: 14
Joined: Wed Dec 10, 2003 12:19 pm

Lookup Problem

Post by chou »

Hi All,

My Data is coming from source with state code CA, NJ, MI, NULL, IL, KY..... and lookup OCI is having all 54 state code when i am doing lookup in stage and the cond is If field.StateCD = "NULL" or " " then "XX" else field.StateCD.

but the result records is coming 389, 611 is to rejects.

Is any thing worng in code. please help me.

Thanks
jreddy
Premium Member
Premium Member
Posts: 202
Joined: Tue Feb 03, 2004 5:09 pm

Post by jreddy »

Lookup stage has a property called 'IF not found' which you have to set according to what you want, if you use 'Continue' it will send the record along to its output link with Null or a 0 depending on that column format. I did a similar thing earlier, where lookup was on a number column, so it returned 0 when it didnt find corresponding record in lookup table...

as far as i know..there is no way you can put that logic in the Lookup stage

what you can do in a case such as yours is, have a transformer either before or after lookup to put that logic in the derivation for that column. I would use a transformer after the lookup stage, to replace null as whatever default value you wish to have. Coz i have successfully implemented that in my case..when it returned a 0, i replaced it with some 999999 value.
datastage
Participant
Posts: 229
Joined: Wed Oct 23, 2002 10:10 am
Location: Omaha

Post by datastage »

I'd followed jreddy's advice and have a transform after the lookup.

Also, just to make sure since this is how you typed it into the post,
If field.StateCD = "NULL" will look for a string constisting of N,U,L,L. Is the source data has a true null as opposed to the string word "NULL" then us the IsNull() function to test.
Byron Paul
WARNING: DO NOT OPERATE DATASTAGE WITHOUT ADULT SUPERVISION.

"Strange things are afoot in the reject links" - from Bill & Ted's DataStage Adventure
Post Reply