Lookup failure not passing NULL

dsusersaj · Post by **dsusersaj** » Thu Jul 22, 2010 4:25 pm

In my look up stage , in the output I introduced this new field named lkpval which is varchar. I am setting this to a key value from the reference link. But when the lookup fails this field (lkpval) is not set to NULL. It is just set to blank.I want to explicitly check for NULL. As blank can also be a key field. lkpval is set as nullable.

Can anyone suggest what could be wrong here?.

Thanks.

arun_im4u · Post by **arun_im4u** » Thu Jul 22, 2010 4:41 pm

How did you determine that the output is blank and not null? Did you write to a File, dataset or table?.. Or the constraint set in a transformer/filter is not working as expected..

dsusersaj · Post by **dsusersaj** » Thu Jul 22, 2010 4:44 pm

arun_im4u wrote:How did you determine that the output is blank and not null? Did you write to a File, dataset or table?.. Or the constraint set in a transformer/filter is not working as expected..

OK..I did both..I wrote this to a file.

like..isnull(lkpval) ..The value i am getting is 0.

arun_im4u · Post by **arun_im4u** » Thu Jul 22, 2010 4:49 pm

Then the key column you are selecting from in the lookup ( Reference Link) is set to Nullable NO. Please set it to Yes.

dsusersaj · Post by **dsusersaj** » Thu Jul 22, 2010 5:43 pm

arun_im4u wrote:Then the key column you are selecting from in the lookup ( Reference Link) is set to Nullable NO. Please set it to Yes.

Ok..Thats right. The key column that I am setting this field to is not nullable. But I thought the target field is set to null when the look up fails. SO now my assumtion is incorrect.

I don't want to make the key column nullable. Instead , I set lkpval to a non-key field which is nullable coming from reference link.This is also not working.It is giving me an error. As I am not in front of the work computer now , I can't recollect that error.

ray.wurlod · Post by **ray.wurlod** » Thu Jul 22, 2010 7:48 pm

Make the key column nullable.

"Key" here does not mean primary key, it means "search key" and will return a NULL if the lookup fails. So it has to be nullable.

dsusersaj · Post by **dsusersaj** » Thu Jul 22, 2010 7:58 pm

ray.wurlod wrote:Make the key column nullable.

"Key" here does not mean primary key, it means "search key" and will return a NULL if the lookup fails. So it has to be nullable. ...

Oh..Ok..I will try that.Thanks for the explanation..

dsusersaj · Post by **dsusersaj** » Thu Jul 22, 2010 8:42 pm

ray.wurlod wrote:Make the key column nullable.

"Key" here does not mean primary key, it means "search key" and will return a NULL if the lookup fails. So it has to be nullable. ...

So Can't I use the non -key nullable field for lkpval?.Why is n't working?.

ray.wurlod · Post by **ray.wurlod** » Thu Jul 22, 2010 8:47 pm

Anything that might return NULL must be nullable. You can use any field you wish for comparison, but it must match data type and size (and, presumably, the occasional value).

dsusersaj · Post by **dsusersaj** » Thu Jul 22, 2010 9:03 pm

ray.wurlod wrote:Anything that might return NULL must be nullable. You can use any field you wish for comparison, but it must match data type and size (and, presumably, the occasional value). ...

The non key field in reference is col1 (varchar) nullable with no size mentioned.
Also lkpval is also varchar nullable with no size mentioned. So when the look up fails, col1 should have null value in it and if the lkpval is mapped to col1 ,lkpval should also have null value right?.

ray.wurlod · Post by **ray.wurlod** » Thu Jul 22, 2010 9:31 pm

Not right.

You can not supply NULL as a lookup value. You can not compare NULL. Nothing equals NULL (at least you can not state legitimately that it does).

You will need to convert your NULLs into in-band nulls.

dsusersaj · Post by **dsusersaj** » Thu Jul 22, 2010 9:39 pm

ray.wurlod wrote:Not right.

You can not supply NULL as a lookup value. You can not compare NULL. Nothing equals NULL (at least you can not state legitimately that it does).

You will need to convert your NULLs ...

If you refer to my original question , lkpval is the target field which is used to identify the lkp fail or success based on NULL or NOT NULL in it. My question is that if I pass a nonkey value from my reference link which is nullable to lkpval,it should have NULL when the lookup fails..??..

ray.wurlod · Post by **ray.wurlod** » Thu Jul 22, 2010 9:45 pm

THAT is correct, but you can not pass null to be looked up, even though the field is defined as nullable.

dsusersaj · Post by **dsusersaj** » Thu Jul 22, 2010 9:49 pm

ray.wurlod wrote:THAT is correct, but you can not pass null to be looked up, even though the field is defined as nullable. ...

The lookup is on the key field col1 but I am passing the non-key field col2 to the target field lkpval to check for lookup success or failure. Is that correct?..

ray.wurlod · Post by **ray.wurlod** » Thu Jul 22, 2010 9:50 pm

How would I know? You pass the value you want to look up, and link it to the field in which it is likely to occur in the reference data set.