DSXchange

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.

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"..

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.

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).

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.

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

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.

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

Thanks Ray.

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