[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
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
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