hash file lookup

[vinsashi]

hi,
data loaded to from oracle db to seq file and hahsfiles and key columns are column1 and column2. but column2have null data.in the next job joined based on key columns both seq file and hash file but condition not failed even though null values in column2.could you please clarify seq file and hash file how will handle null data ..

[ray.wurlod]

Firstly, the correct nomenclature is "hashed" file, not "hash" file.

It's perfectly OK for non-key fields to contain NULL, just as it would be in a database join.

The hashed file even allows for part of the key to be null, in a multi-column key. (You may disagree with this, but it's how it works.)

Provided that the fields are marked as nullable, the field value - whether NULL or not - will be transferred to the output link of the Transformer stage where there is a key match.

[SURA]

but column2have null data.

1. Is it really NULL?

2. Can you able to query the data in Oracle where col2 is null?

[vinsashi]

Hi,
below is my sample query. where column2 have null data in both tables. if execute in db no records.
SELECT * FROM table1 a,table2 b
WHERE a.column1=b.columns1
and a.column2=b.column2

same scenario in datastage job its passing records to output.
by using seq file and hashed file. its ignoring column2 join.

[chulett]

Is your hashed file lookup set up properly? Are you certain you've set up both Column1 and Column2 as keys in the hashed file so both are passed to the hashed file from the transformer and used for the join?