Lookup stage is returning a value even on lookup failure

[tutul]

Hi All,

I have a job which looks up from a file and takes some columns on matching to the target table as shown below.

lookupup fileset
|
source--->lookup stage---->target table

The lookup stage has conditions set to CONTINUE in case of lookup failure. So I believe even when the key columns dont match I should get the columns from lookup file set as NULL.
I am not getting NULL values in the non-key lookup columns which are brought to the target table, even though the key columns dont match. I am getting 0 in col1 (integer), " " in col2(varchar2 10).

When I am using the server job, with hash file and tranformer , I am getting NULL values for col1 and col2.

Please suggest.

[balajisr]

Is non key column nullable?

[SAMEENA]

Hi,
If the column is non nullable then in case of lookup failure , the returned value is 0 or empty string depending upon the datatype of the column.

[tutul]

Thanks all,

The lookup file set was populated with non-nullable as the non-key fields by another job. The actual job had the non-key columns as nullable.

I changed the metadata of the look-up fileset job to Nullable. The issue is resolved.

[ds_debasis]

Hi All,

I have a job which looks up from a file and takes some columns on matching to the target table as shown below.

lookupup fileset
|
source--->lookup stage---->target table

The lookup stage has conditions set to CONTINUE in case of lookup failure. So I believe even when the key columns dont match I should get the columns from lookup file set as NULL.
I am not getting NULL values in the non-key lookup columns which are brought to the target table, even though the key columns dont match. I am getting 0 in col1 (integer), " " in col2(varchar2 10).

When I am using the server job, with hash file and tranformer , I am getting NULL values for col1 and col2.

Please suggest.

Hey ! I'm sure you have checked this but just for information, Did you check the Lookup Failure option ? I think we need to keep it as wither Drop or Rejected. Try it doing Rejected and check the rejected link (temporary create it in lookup).