Join/Lookup Stage output

Gopinath · Post by **Gopinath** » Fri Feb 13, 2009 8:05 am

Hi,

Iam using Join/Lookup stage in one of my Jobs. Two Input datasets performs Left Outer Join, when there is no hit then the columns from right dataset should give a empty space or NULL(Data type is Char/varchar). What is the difference between "Space" and NULL.

I have used Null function and also Len(C1)=0 to assign a default value as "X" to the varchar/char field but none of the function is working. Then i captured the Join output in a seqential file, which gave a special character, kind of box.

Is this output from a Left Outer Join for non hit records. How to handle this?
Thanks.

verify · Post by **verify** » Fri Feb 13, 2009 9:08 am

Space and ASCII are two different characters having different ASCII values to each.

For the unmatched records the value usually datastage will give as "NULL"..

Gopinath · Post by **Gopinath** » Fri Feb 13, 2009 12:02 pm

verify wrote:Space and ASCII are two different characters having different ASCII values to each.

For the unmatched records the value usually datastage will give as "NULL"..

I totally agreed to ur point, but why the non hit output record is giving some junk values instead of NULL. How to handle this situation.
Thanks.

ray.wurlod · Post by **ray.wurlod** » Fri Feb 13, 2009 2:08 pm

Use a hex editor to find out what the "square box" actually is.

Space should be 32, NULL will probably be 128 (the internal value used to represent an out of band null in DataStage on UNIX).

Gopinath · Post by **Gopinath** » Sat Feb 14, 2009 12:20 pm

Thank You ray, but we dont have hex editor installed in our machine. Will try for that asap, but now all i have to do is, i need to fix this issue in data stage for which i need support from people over here.
Thanks.

ray.wurlod · Post by **ray.wurlod** » Sat Feb 14, 2009 1:46 pm

You DO have a hex editor - it's the od -x command in UNIX.

Gopinath · Post by **Gopinath** » Mon Feb 16, 2009 5:11 am

Hi Ray,

Nullability is working now, the change made is, the data type for the columns coming from the right link is changed to YES. But why we need to define it in the input link to the join stage itself.
All my records in both the input links are Non nullability records and giving to the Left Outer Join type and output should determine the nullability right..?
Now both my input(right link) and output link has nullabilty as YES. Is this a standard way to define. Can you please guide me on this.
Thanks.

ray.wurlod · Post by **ray.wurlod** » Mon Feb 16, 2009 2:31 pm

The right input can return NULLs in a left outer join, and therefore must be Nullable.

Gopinath · Post by **Gopinath** » Mon Feb 16, 2009 10:19 pm

Thanks Ray.

bputta · Post by **bputta** » Wed Mar 09, 2011 5:46 pm

Thanks Gopi for the info, this helped clear my doubt of why the Nulls are not getting loaded to outfile after the join.

DSXchange

Join/Lookup Stage output

Join/Lookup Stage output

This post helped me a lot.