Look up Issue

pavan_test · Post by **pavan_test** » Mon Oct 08, 2007 4:47 pm

Hi All,

I have 2 sources. Source1 and soure2.

I have a column in source1 as City and i have similar column in source 2 i.e City.

There is another column called "ZIP" in my source1 and also source2.

All of them are varchar.

If city from source1 matches with city from source2 then I have to move the value of "ZIP" from source2 to the "ZIP" in the source 1 and to the output of the Join stage. If there is no match then for that particular record in the output the value of "ZIP" from source 1 should be null or blanks.

This is how requirement came to me;

"Keep all the records from Source1 "

if Source1.city = source2.city then
move source2.ZIP to source1.ZIP
else
output blanks to source1.zip
end if

I am doing a left outer join in my datastage job where source1 is my left link.

when source1.city matches with source2.city then Join is moving the source2.ZIp to source1.zip which is as I expected. but when there is no match i am expecting blanks or nulls for the source1 ZIP column but I am getting the same value as input.

CAn anyone please suggest me any work around for this problem.

Thanks & Regards
PA

ray.wurlod · Post by **ray.wurlod** » Mon Oct 08, 2007 8:51 pm

What stage type are you using? If it's a left outer join, you should be getting NULL as the return from the right input. You need some downstream processing to convert these NULLs to whatever replacement value you desire.

pavan_test · Post by **pavan_test** » Mon Oct 08, 2007 10:45 pm

Thanks Ray

Regards
Pa

Ramani · Post by **Ramani** » Mon Oct 08, 2007 11:55 pm

Please allow the ZIP columns to allow NULL values in the stages. (there is a check box in the columns definition for each columns in any stages). Then you should be getting NULL values out, otherwise, you may get some default values out from the stage if you donot allow NULL values for that column. Hope this helps.