Join/Lookup Stage output
Moderators: chulett, rschirm, roy
Join/Lookup Stage output
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.
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.
Gopinath
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.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"..
Thanks.
Gopinath
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
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).
Space should be 32, NULL will probably be 128 (the internal value used to represent an out of band null in DataStage on UNIX).
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
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.
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.
Gopinath
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
This post helped me a lot.
Thanks Gopi for the info, this helped clear my doubt of why the Nulls are not getting loaded to outfile after the join.
bharath