Lookup warning on null

[muralisankarr]

Source Employees:
record
(
EMPLOYEE_ID:int32;
FIRST_NAME:nullable string[max=20] {prefix=2};
LAST_NAME:string[max=25] {prefix=2};
EMAIL:string[max=25] {prefix=2};
PHONE_NUMBER:nullable string[max=20] {prefix=2};
HIRE_DATE:timestamp;
JOB_ID:string[max=10] {prefix=2};
SALARY:nullable decimal[8,2];
COMMISSION:nullable decimal[2,2];
MANAGER_ID:nullable int32;
DEPARTMENT_ID:nullable int32;
)
Lookup Departments:
record
(
DEPARTMENT_ID:int32;
DEPARTMENT_NAME:string[max=30] {prefix=2};
MANAGER_ID:nullable int32;
LOCATION_ID:nullable int32;
)

We are performing a Lookup with th a above two tables for the below target (of dataset)
Target:
record
(
EMPLOYEE_ID:int32;
FIRST_NAME:nullable string[max=20] {prefix=2};
LAST_NAME:string[max=25] {prefix=2};
EMAIL:string[max=25] {prefix=2};
PHONE_NUMBER:nullable string[max=20] {prefix=2};
HIRE_DATE:timestamp;
JOB_ID:string[max=10] {prefix=2};
SALARY:nullable decimal[8,2];
COMMISSION:nullable decimal[2,2];
MANAGER_ID:nullable int32;
DEPARTMENT_NAME:string[max=30] {prefix=2};
LOCATION_ID:nullable int32;
)

The department_id in source and the target are defined as Nullable. But inspite of this setting I am getting the below error messages
LookupEmpDep,0: When binding input interface field "DEPARTMENT_ID" to field "DEPARTMENT_ID": Converting nullable source to non-nullable result; fatal runtime error could occur (use a modify operator to specify value to which null should be converted)
LookupEmpDep,0: Null in field "DEPARTMENT_ID ": Result is non-nullable and there is no handle_null to specify a default value
LookupEmpDep,0: Failed a keylookup for record.
And the job is aborted. I have used the modify operator to handle this. But I need to understand why we got these two messages? I have mapped the columns to and from not null. Please explain

ManyThanks
MSR

[nikhilanshuman]

You are getting the warning because you are trying to pass NULL to a NOT NULL column.Check these scenarios :

1st scenario :
check the mapping in "LookupEmpDep".Compare the nullability of "DEPARTMENT_ID" with the column to which it is mapped.It seems that the target column in "Not Null".The situation might be like

DEPARTMENT_ID (NULL) -----> DEPARTMENT_ID(NOT NULL)

Solution : Check for each mapped column in the job and change the nullability to "NULL"

2nd scenario :
This situation might occur if in the table the Column in nullable and in the input column datatype ,it is defined as Not null

Reason of failure :
In lookup properties following is set :

If the lookup fails --> Abort

Change Abort to "Continue" or "Drop" to avoid failure of the job.

[muralisankarr]

1) The department id from the primary link is nullable while the same column from the reference is not-nullable. Lookup stage use these column and send the output where the department id from the primary input is mapped. The other columns from the reference link is changed to nullable.

2) The Lookup failure condition is set to Continue

Beside all these I'm getting the same error message. Please help

[chulett]

Make it nullable in the lookup. If you really want a non-nullable result, handle the null in a Modify stage after the lookup and make that field not nullable from that point onward.

[nikhilanshuman]

The Nullability of source and reference keys should be same.
Also,the key columns on which the lookup is performed must be NOT NULL.

Do this :

In the source query,use NVL(Department_ID,-1)
Set the nullability to Not Null in source itself.

=>The source and refernece link columns should be NOT NULL.

After doing this,you will not get the warning.

[muralisankarr]

The Nullability of source and reference keys should be same.

I mentioned in my first post that I managed to get the data moving with the help of modify stage. What I need to understand is why the foreign key should not be nullable? Also the handle null or nvl function will introduce a in-band null value in to the file.

[muralisankarr]

Make it nullable in the lookup. If you really want a non-nullable result, handle the null in a Modify stage after the lookup and make that field not nullable from that point onward.

Thanks Chulett. But why we need to keep the primary key and the foreign key as NULLABLE? Just need to understand the OSH method/mechanism behind this

[chulett]

There are several stages that assume nullable output, the lookup and aggregator for example. Regardless of what you know, they will assume nullable and force you to treat it as such and handle nulls 'properly' to make them happy.

[ray.wurlod]

All fields from the reference input must be nullable because all fields will be set to null if the lookup fails.

The lookup key from the stream input should also be (made) nullable because it has to map to the lookup key on the reference input.

Any output fields derived from the reference input must be null where the Lookup Failed rule is Continue, because the failed lookup rows will be returning nulls.

[muralisankarr]

Many Thanks for chulett and ray.