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
Lookup Problem
Moderators: chulett, rschirm, roy
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.
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.
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.
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
WARNING: DO NOT OPERATE DATASTAGE WITHOUT ADULT SUPERVISION.
"Strange things are afoot in the reject links" - from Bill & Ted's DataStage Adventure