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

2019 GARNER MAGIC QUADRANT FOR DATA INTEGRATION TOOL

Talend named a Leader.

Get your copy

OPEN STUDIO FOR DATA INTEGRATION

Kickstart your first data integration and ETL projects.

Download now

What’s New for Talend Summer ’19

Watch the recorded webinar!

Watch Now

Best Practices for Using Context Variables with Talend – Part 4

Pick up some tips and tricks with Context Variables

Blog

How Media Organizations Achieved Success with Data Integration

Learn how media organizations have achieved success with Data Integration

Read

Definitive Guide to Data Quality

Create systems and workflow to manage clean data ingestion and data transformation.

Download