Page 1 of 1
IsNULL function in Lookup Columns
Posted: Thu Jul 10, 2008 9:05 am
by satheesh_color
Hi All,
I have a problem with the below constraint in one of my jobs
IsNULL(Lkp.Column1) AND IsNULL(Lkp.Column2)
both Column1 and Column2 in the hashed file are key fields.How to validate the above constraint. I have checked my hashed file,none of the columns are null.Please let me know your thoughts on this issue.
Thanks,
Satheesh
Posted: Thu Jul 10, 2008 9:11 am
by chulett
What are you trying to accomplish with this constraint? If it is simply to check the success/failure of the lookup, that should work but the 'more proper' solution would be the NOTFOUND link variable.
Posted: Thu Jul 10, 2008 3:20 pm
by ray.wurlod
What is the problem that you are having?
The expression is syntactically correct. The key columns will not be null unless the lookup fails (a lookup is functionally a left outer join).
Posted: Thu Jul 10, 2008 10:25 pm
by chulett
Hmmm... ok. Care to post your resolution?
Posted: Fri Jul 11, 2008 12:04 am
by satheesh_color
Thanks for your comments.
The key columns in the hashed files should not be null. So i am confused with IsNull instead of NOTFOUND link variable.
Thanks,
Satheesh
Posted: Fri Jul 11, 2008 12:22 am
by chulett
So we're not resolved? You are correct that you populate the key columns with non-null data from the input stream so they "should not be null". However, when the lookup fails all columns return null (including the keys) so they can be checked for null post-lookup to determine failure.
The NOTFOUND Link Variable replaces that technique with a status you can check directly.