DSXchange

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.

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.

I am agree with Ray. Join is not permitted on NULL.

Alternatively, you can handle NULL before performing the join operation and assign some value say '-999999' and revert it back to NULL once you are done with your join operation.

i checked the join conditions by creating two tables and having nulls. A null in source is joining with a null in target..so even if i change it to some other value like -1, it is going to give me the same ouput.

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.