Lookup not found return key ?

[inadeau]

Hi,

I'm doing a lookup using Lookup Stage (but the same error is appenning with Join stage). Both Input file and Lookup fileset have the KEY field to retreive the VALUE field from Lookup fileset. The output file contains KEY and VALUE fields mapped from the Input and Lookup files.

The problem I'm facing is when the lookup is unsuccessful, the output VALUE field is populated with the KEY field value. I would have expected a NULL... Is that normal or am I missing a detail ?

Thanks !

Isabel

[ArndW]

Isabel,

this is a bit tricky in Px; if the column is NULLable then a null value will be returned, otherwise a default value will come back (and I'm not sure where that is to be found, as I am trying it with a CHAR(3) column and it looks like 3 spaces but it is, and it's not null either.

[T42]

Okay, lets make sure we are on the same page:

1. You are doing a lookup stage, with the "Continue" option -- ensuring that the data continues.

2. You have an input link to the lookup with the following fields: KEY, VALUE.

3. You have a reference link to the lookup with the following fields: KEY, VALUE.

4. You have an output link from the lookup with the following fields: KEY, VALUE.

If this is exactly how your situation is, then...

The answer is pretty straightforward -- it is most likely that you are mapping the input's KEY and VALUE to the output link. If you wish to retain the actual lookup value, you should rename the "VALUE" field to a different name.

The NULL condition is highly dependent on the nullability of the lookup's reference link's field. If the reference field is nullable, the lookup stage will append a NULL value to the "Continue" result. Naturally, if the output field is not nullable, the lookup stage will abort.

If the reference field is not nullable, then APT_STRING_PADCHAR value will be used to fill out the field.

If my assumptions are wrong, please correct them. Thank you.