IsNULL function in Lookup Columns

satheesh_color · Post by **satheesh_color** » Thu Jul 10, 2008 9:05 am

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

chulett · Post by **chulett** » Thu Jul 10, 2008 9:11 am

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.

ray.wurlod · Post by **ray.wurlod** » Thu Jul 10, 2008 3:20 pm

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).

chulett · Post by **chulett** » Thu Jul 10, 2008 10:25 pm

Hmmm... ok. Care to post your resolution?

satheesh_color · Post by **satheesh_color** » Fri Jul 11, 2008 12:04 am

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

chulett · Post by **chulett** » Fri Jul 11, 2008 12:22 am

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.