topic tMap left outer join does not return null for unmatched row in Talend Studio

tMap left outer join does not return null for unmatched row

umut_gulac — Sun, 09 Oct 2016 13:18:40 GMT

Hi folks,
I use tMap for left outer join to multiple lookup tables. It returns NULL in the non key columns for unmatched rows as expected; however it DOES NOT return null but returns 0 (numeric zero) for key columns (no matter whether they are set as key in the schema) for unmatched rows. This is confusing, expected or not?
Please see in the screenshots below.
Product_Category_SK is set to 0 for unmatched rows; however I expect that to be NULL. This is set as key in the lookup schema, where in the MySQL Product_Category table, it is a PK.
Product_Price_SK is set to 0 for unmatched rows; again expected to be NULL. This was not set as key in the lookup schema, where in the MySQL Product_Price table, it is a PK.
How can I get them NULL for the unmatched rows?

Re: tMap left outer join does not return null for unmatched row

Anonymous — Sun, 09 Oct 2016 15:49:26 GMT

I suspect (I cannot see screen shots) this is related to having int values defined as NOT NULL. This is a complete guess, so may be way off, but I would check this.

Re: tMap left outer join does not return null for unmatched row

umut_gulac — Sun, 09 Oct 2016 21:56:19 GMT

I suspect (I cannot see screen shots) this is related to having int values defined as NOT NULL. This is a complete guess, so may be way off, but I would check this.

I checked that in tMap out schema and the output component (tMysqlSCD) schema. There I confirmed that I had set those Integer columns as Nullable.
Let me paste the whole job as well.

Re: tMap left outer join does not return null for unmatched row

umut_gulac — Wed, 19 Oct 2016 09:23:46 GMT

Any one, who has an idea about this, expected or problem ?