I've got a job which has tries to lookup on db2 using some nullable fields.
If I use a 'normal' lookup, the lookup correctly finds the matching row on db2 that has null values in the fields...
however, if I use a sparse lookup then it does not find the matching row.
Is there a difference between how nulls are handled for the sparse and normal lookups against db2 ? Any other ideas for what I might be doing wrong?
Thanks
Lookup null value on DB2 - different for sparse and normal
Moderators: chulett, rschirm, roy
Hello Steve,
there is nothing fundamentally different in how nulls work that I'd noticed before. I trust that both the job metadata as well as the database definitions are nullable for all those fields and are identical with each other, but I'm sure you've already double-checked that. Can you make a simple test case with just one (nullable) column as the key and see if there is a difference or perhaps it just affects the outcome with more than one column.
there is nothing fundamentally different in how nulls work that I'd noticed before. I trust that both the job metadata as well as the database definitions are nullable for all those fields and are identical with each other, but I'm sure you've already double-checked that. Can you make a simple test case with just one (nullable) column as the key and see if there is a difference or perhaps it just affects the outcome with more than one column.
Hi Arnd.
I've set up a test job with just 1 key column and using 1 input record which should match with the one record on the table ( both have null in the field I've set as the key in the datastage lookup).
If I set the lookup type to 'normal' the lookup finds the record. If I set it to 'sparse' the lookup doesn't find the record.
Both the input and the reference links have the metadata set to allow nulls in that column.
I've set up a test job with just 1 key column and using 1 input record which should match with the one record on the table ( both have null in the field I've set as the key in the datastage lookup).
If I set the lookup type to 'normal' the lookup finds the record. If I set it to 'sparse' the lookup doesn't find the record.
Both the input and the reference links have the metadata set to allow nulls in that column.