[resolved] left outer join outputs 0 instead of NULL

[Anonymous]

Hi
I am loading data into a database table that has a nullable foreign key reference to a parent table. The logic is as follows - if the delimited file layout has the parent identifier field populated - lookup the parent table and get the value of the parent primary key(foreign key for child table) - otherwise load the record with foreign key as NULL
For this i do a left outer join on my map. However the outer join output for the null lookup is 0 - which results in an FK violation when it comes to the database.
Screen shot is below. Any tips on how to handle this
Regards
Sid

[Anonymous]

does your schema permit nulls for that column?  If not you will get a default, 0  for numbers.

[Anonymous]

yes the schema permits null for the column(type Long). Screen shot below.
Basically because the input is null - no row will be found - hence we need not put in any value in the output field. Want it to go as NULL

[Anonymous · Accepted solution]

Hmm... I suspect that because hcp_id is a non null key column on the input it is getting treated as not null.  Can you try changing the input metadata to test this idea?

[Anonymous]

Mike
That was it.
Sid