nullable fields in hash file and oraoci stages

[kollurianu]

Hi All,

I have three fields in my odbc lookup stage that is (composite key)

f1, f2, f3, can i have null values in f2 and f3 fields still use it for lookup.


similarly can have nullable field as key field in the hash field,

thank you all for your answers in advance.

[sivatallapaneni]

I dont think you can have null fields as key in a hash file. At least the hash file wouldn't allow the null key fields.

I'm not sure about ODBC lookup. My guess is it is ok to have.

[chulett]

No, it's not ok.

A lookup is an equi-join where field = key and what is equal to null? Yup, that's right - nothing.

[dzdiver]

I am using version 7.1

I have a job that selects from database and looks up a hash file with 3 key columns plus a lookup column.

The hash file was built from a database select and in some rows, one of the key column values were null.

some of my select result:-
(note the last 3 values are the key cols)
4600,'G','GPRS5','IMODE'
...
4602.'G','IMDFRM','FREE IMAIL'
...
4603,'Q','IMAIL',NULL

The job itself selects from a table and matches into the hash file on three columns. Some of the rows in source data have data in all three columns and other rows have nulls in one of the columns, which matches the hash file.

The cases where all columns are populates works correctly, as do the cases where there is null in a key column, correctly matching on the other populated 2 key columns.

so with incoming data like:-
'G','GPRS5','IMODE'
'G','IMDFRM','FREE IMAIL'
'Q','IMAIL',NULL

I get out the warehouse keys
4600
4602
4603



HTH,
Brian.