topic Re: [resolved] left outer join outputs 0 instead of NULL in Talend Studio

[resolved] left outer join outputs 0 instead of NULL

Anonymous — Fri, 30 Jan 2015 15:13:45 GMT

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

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

Anonymous — Fri, 30 Jan 2015 16:48:21 GMT

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

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

Anonymous — Fri, 30 Jan 2015 17:04:48 GMT

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

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

Anonymous — Fri, 30 Jan 2015 17:29:22 GMT

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?

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

Anonymous — Fri, 30 Jan 2015 18:31:53 GMT

Mike
That was it.
Sid