[resolved] left outer join outputs 0 instead of NULL

Five Stars

[resolved] left outer join outputs 0 instead of NULL

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

Accepted Solutions
Highlighted
One Star

Re: [resolved] left outer join outputs 0 instead of NULL

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?

All Replies
One Star

Re: [resolved] left outer join outputs 0 instead of NULL

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

Re: [resolved] left outer join outputs 0 instead of NULL

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
Highlighted
One Star

Re: [resolved] left outer join outputs 0 instead of NULL

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?
Five Stars

Re: [resolved] left outer join outputs 0 instead of NULL

Mike
That was it.
Sid