Not NUll Comparision

nkln@you · Post by **nkln@you** » Wed May 23, 2007 1:20 am

I do have a look up in Job which has 5 keys. I need to compare 4 keys in hash file with that of 4 fields in source. The 5th field in hash file should be not be Null. How can I give Not Null in the derivation field for that 5th Filed inside Transformer. If we want to cpmpare it with Null , we can use @NULL. I need to make sure that the field in Hash file is not null.

ray.wurlod · Post by **ray.wurlod** » Wed May 23, 2007 1:33 am

NONE of the key columns in a hashed file can be null.

If the reference key expression is null, the lookup is not attempted. You can prove this using the Debugger.

nkln@you · Post by **nkln@you** » Wed May 23, 2007 9:10 am

Ray,

My aim is actually not to make sure the 5th field is NOT NULL.My aim is to compare only 4 fields of Hash file with source. I dont want to compare 5th filed with source.Since 5th filed is also a key in hash file, it has to be compared with some value, but for my requiremnet I dont want.

Even I cannot create that hash file with only 4 keys, because that file is used by other jobs.

So, is there any solution to compare only 4 fields and making 5th field as dummy.

DSguru2B · Post by **DSguru2B** » Wed May 23, 2007 9:33 am

Do a lookup against the hashed file via UV stage. With the hashed file, it has to be lookedup with all the keys and all the keys need to match to grab a record.

nkln@you · Post by **nkln@you** » Wed May 23, 2007 10:41 am

I am new to UV stage. What additional advantage I have if I use UV stage instead of OCI stage. For Both UV and ORA, we need to mention table names, columns, where clause etc.

DSguru2B · Post by **DSguru2B** » Wed May 23, 2007 1:09 pm

UV stage will read your hashed file. OCI stage cannot do that.

mystuff · Post by **mystuff** » Thu May 24, 2007 7:24 am

NONE of the key columns in a hashed file can be null.

If the reference key expression is null, the lookup is not attempted. You can prove this using the Debugger.

Hi Ray,

Can we trim the Key field before writing to hashed file. And while doing the lookup, can we do lookup with empty string like ''?

ray.wurlod · Post by **ray.wurlod** » Thu May 24, 2007 2:53 pm

Zero-length string is valid as a key value. NULL is not.

sun rays · Post by **sun rays** » Fri May 25, 2007 12:07 pm

nkln@you wrote:Ray,

My aim is actually not to make sure the 5th field is NOT NULL.My aim is to compare only 4 fields of Hash file with source. I dont want to compare 5th filed with source.Since 5th filed is also a key in hash file, it has to be compared with some value, but for my requiremnet I dont want.

Even I cannot create that hash file with only 4 keys, because that file is used by other jobs.

So, is there any solution to compare only 4 fields and making 5th field as dummy.

Just for this job purpose, you can create another hashed file with 4 keys instead of modifying the original file, and the fith key from source can be checked for null value in the transformer.