Search code examples
talend

Talend tMap left outer join outputs zero instead of null for unmattched lines in first table

A left join by default for two table T1 and T2, return all the lines in table T1 joined by matching results in table T2, for the lines of T1 with no matches in T2, the left join complete them with nulls

lets say T1 contains the following line 

| id | class | student_id | 
|-------------------------|
|  1 | math  |   null     |
|  2 |  svt  |      1     |

and T2 contains the following line:

| id | name | 
|-----------|
|  1 | rach |

the result of T1 left joined to T2 

select * 
from T1 left join T2 on T1.student_id = T2.id

would be something like ( i abstracted a lot of details to show case issue )

| id | class | student_id |  id |  name |
|---------------------------------------|
|  1 | math  |   null     | null|  null |
|  2 |  svt  |      1     | 1   |  rach |

As an aftermath doing a left join using tMap i expect an identical behavior, more importantly the unmatched lines should be filled with null instead of zeros

enter image description here

enter image description here

the above pics shows a much simplified version of the experiment to show case the issue in Talend in bref for unmatched lines in table res_partner the output sales_rep_key has a value of zero instead of null

can anyone explain this to me.

Solution

  • Much of it depends on how the input data is for your use case, both for the main and lookup rowset. If I take your sample database case and implement that in Talend I receive the output as per your expectations. NULL records for all unmatched row/columns.

    As per your demo I see you are trying to perform something like - (sales_rep.id == 0) ? context.sales_rep_unko But I feel that you should be using like this (below - any one of them from the combination would do) instead when there will be non-matching rows because of left-join in tMap (Relational.ISNULL(sales_rep.id) || sales_rep.id.isEmpty() || sales_rep.id.toString() == null)