Lookup failure not passing NULL
Moderators: chulett, rschirm, roy
Lookup failure not passing NULL
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.
Can anyone suggest what could be wrong here?.
Thanks.
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.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.
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.
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
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.
"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.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
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).
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
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?.
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
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.
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.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
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 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 ...
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact: