lookup generating null string

Mat01 · Post by **Mat01** » Thu Feb 16, 2006 9:12 pm

Hi All,

I have a problem with the values returned by a lookup. My field is defined as Char (1) and some of the values are not matched. I have set the lookup to continue in this case. My understanding is that the value returned by the lookup will be set to null when no match was made.

However, the lookup seems to return an unindentified value. When I try to use the function IsNull(Value) in a transformer, it returns false. The test Trim(Value) = '' will also return false. But when I try AlNum(Value) the function fails telling me that a null string is being passed to the function???

When I dump the result in a flat file, I see that the value for the unmatched field is unreadable in ascii but correponds to the Hex value 0x00. This should be a Null, shouldn't it?

Any ideas how DataStage might interpret this field?

Thanks,

Mat

Peytot · Post by **Peytot** » Fri Feb 17, 2006 12:36 am

Hello Mat,

In my case, I work around : 2 possibilities
- In adding a field in mu lookup file Set with a default value. After the lookup, I test the value in my transform.
- In using the option "reject" in the look up stage.

Regards,

Pey

ray.wurlod · Post by **ray.wurlod** » Fri Feb 17, 2006 1:46 am

0x00 should correspond to "" rather than to NULL. You could always test against this explicit character.

0x00 is known in ASCII as NUL or "the null byte", which is where most of the confusion originates. This character is used in C programming to terminate a string. Clearly a string containing nothing but this characters is seen as empty.

Mat01 · Post by **Mat01** » Fri Feb 17, 2006 8:51 am

Thanks for your answers. I found the reason why it didn't work. The lookup will put a null in your nullabe output field if the corresponding input field is also nullable. Otherwise, the field gets a default value which does not respond to the tests: IsNull() or = ""

Thanks,

Mat

kumar_s · Post by **kumar_s** » Sat Feb 18, 2006 2:29 am

ray.wurlod wrote:0x00 should correspond to "" rather than to NULL. You could always test against this explicit character.

0x00 is known in ASCII as NUL or "the null byte", which is where most of the confusion originates. This character is used in C programming to terminate a string. Clearly a string containing nothing but this characters is seen as empty.

Inorder to avoid this confusion i ll make sure APT_STRING_PADCHAR in administrator setting will be changed frin 0x0 to ' '.