Datastage returns a NULL value as '0' in Lookup stage

hiteshm · Post by **hiteshm** » Wed Sep 12, 2007 8:56 am

Hi,

I am trying to lookup a decimal value (e.g.SQR_CTR_ID) with an integer field (Cont_id) from the target table using a Lookup stage. I am continuing even though the look-up fails. Our undertanding is that if the lookup fails than cond_id (from the lookup table) should be set to null. But at present cont_id is being set to '0'.

Please let me know your comments!!

Rgds,
Hitesh

Maveric · Post by **Maveric** » Wed Sep 12, 2007 9:03 am

I doubt the lookup will ever happen because of different data types of the fields. Also where are are you setting the filed value to NULL if the lookup fails? Check for the nullability of cont_id.

hiteshm · Post by **hiteshm** » Wed Sep 12, 2007 9:27 am

Hi Maveric,

Thanks for your response,

I am looking up both these fields nad trying to output the cont_id from teh referecne link with in the lookup stage. So now where ever there is a mismatch it should return Null to the cont_id,but instead it returns '0'.

The field cont_id is nullable.

Regards,
Saravan

Kirtikumar · Post by **Kirtikumar** » Wed Sep 12, 2007 10:41 am

The field should be nullable on output link after the lookup and am assuming when you mentioned nullable, you meant on the output link.

Are you sure there is no matching rows from the lookup. It might be that matched record has Cont_Id as 0.

Try updating all 0 fpor Cont_Id in lookup to other value than 0 and then check if you are still getting it as 0.

samsuf2002 · Post by **samsuf2002** » Wed Sep 12, 2007 9:25 pm

For int if the look up fails it give 0 if u want null instead of 0, u can convert it in the transformer from valuetonull ,as for data types try using Cont_Id as decimal (lenght,0) .

Minhajuddin · Post by **Minhajuddin** » Wed Sep 12, 2007 11:11 pm

When you get an integer field from a lookup it always returns a '0' instead of a NULL, and a 0000.0000 for a decimal(4,4) and so on..

So this behaviour is expected. If you want a Null instead of a zero.. you can do it in a transformer stage as mentioned by samsuf.

thompsonp · Post by **thompsonp** » Thu Sep 13, 2007 9:39 am

If you want an integer value to return null from a lookup when no match is found you need to make sure the derived integer value is nullable on the reference data input to the lookup as well as the output link.

Marking just the output as nullable doesn't fool datastage. If it was not nullabvle on input it is not nullable on output - and hence a value of zero is 0 where no match is found.