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
Datastage returns a NULL value as '0' in Lookup stage
Moderators: chulett, rschirm, roy
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
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
-
- Participant
- Posts: 437
- Joined: Fri Oct 15, 2004 6:13 am
- Location: Pune, India
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.
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.
Regards,
S. Kirtikumar.
S. Kirtikumar.
-
- Premium Member
- Posts: 397
- Joined: Wed Apr 12, 2006 2:28 pm
- Location: Tennesse
-
- Participant
- Posts: 467
- Joined: Tue Mar 20, 2007 6:36 am
- Location: Chennai
- Contact:
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.
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.
Minhajuddin
<a href="http://feeds.feedburner.com/~r/MyExperi ... ~6/2"><img src="http://feeds.feedburner.com/MyExperienc ... lrow.3.gif" alt="My experiences with this DLROW" border="0"></a>
<a href="http://feeds.feedburner.com/~r/MyExperi ... ~6/2"><img src="http://feeds.feedburner.com/MyExperienc ... lrow.3.gif" alt="My experiences with this DLROW" border="0"></a>
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.
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.