Hi
How does datastage handle null in the input key column and the reference key column.
I have millions of data in the input as well as reference and the key columns which i use to join is having nulls.
So, will the null in input join with the null in reference?. Because i am doing a left join and getting huge number of records which is not correct. I am analysing the problem, but want to know how the nulls are being handled when there is a nullable value in the join key itself.
I am suspecting that it is doing a cartesian product.
question regarding join and null
Moderators: chulett, rschirm, roy
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
Officially (in any form of database query) joining on NULL is not permitted.
For the purposes of the join, you can create a new column containing a particular value (an "in-band null") if your "join key" is null, and the actual value otherwise. Use these created columns to effect the join. They can easily be created using Modify stages.
For the purposes of the join, you can create a new column containing a particular value (an "in-band null") if your "join key" is null, and the actual value otherwise. Use these created columns to effect the join. They can easily be created using Modify stages.
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.
-
- Premium Member
- Posts: 1735
- Joined: Thu Mar 01, 2007 5:44 am
- Location: Troy, MI
In case keys of multiple records are null, how it can defined as key?
in databases join on nulls will not give you any results. but In datastage you will find a match. If you dont want to perform joins on null, filter it out of the reference link.
in databases join on nulls will not give you any results. but In datastage you will find a match. If you dont want to perform joins on null, filter it out of the reference link.
Priyadarshi Kunal
Genius may have its limitations, but stupidity is not thus handicapped.
Genius may have its limitations, but stupidity is not thus handicapped.