One Star

tMap left outer join does not return null for unmatched row

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?





3 REPLIES
Fifteen Stars

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

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.
Rilhia Solutions
One Star

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

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

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

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