One Star

Using left outer join in tMap with other conditions

I have been using the non-inner join lookup and mapping the lookup field to the output and that seems to work well, e.g. if there is a match from my input table to my lookup table, it populates the output with the matching data, otherwise it populates the output with a null value. However, I have hit a snag.
I have GetInvoiceCost as my main input. I have LocationsLUFilt as a lookup. These are joined (non-inner) on GetInvoiceCost.location = LocationsLUFilt.location
What I want to do is if both the input eqnum and location fields are not null, then populate the output location field with the input eqnum field, otherwise, populate the output location field with the lookup location field (so that if there is a match it is populated with the matching value, otherwise it is populated with null)
In my output (InvoiceCostOut) I have the following expression:
!(GetInvoiceCost.eqnum == null) && !(GetInvoiceCost.location == null) ? GetInvoiceCost.eqnum : LocationsLUFilt.location
When I run this, I'm getting values in my location output that don't exist in the lookup table and I can't figure out why.
Shouldn't the non-inner join/filtering for null function work the same regardless of the other activity going on??
(I'm connecting to SQL Server databases, btw)
3 REPLIES
Community Manager

Re: Using left outer join in tMap with other conditions

Hello
When I run this, I'm getting values in my location output that don't exist in the lookup table and I can't figure out why.

As you do a left join, so the records from main rows will be output even though it don't exist in lookup rows, and the columns from lookup rows will be null. eg:
main rows:
id;name
1;a
2;b
3;c
lookup rows:
id;age(nullable)
1;11
2;22
output table:
id;name;age
1;a;11
2;b;22
3;c;null
So, GetInvoiceCost.eqnum and GetInvoiceCost.location are always be populated in your case.
Best regards

shong
----------------------------------------------------------
Talend | Data Agility for Modern Business
One Star

Re: Using left outer join in tMap with other conditions

"As you do a left join, so the records from main rows will be output even though it don't exist in lookup rows, and the columns from lookup rows will be null."
I thought the point of putting the lookup field in the output row was to ensure that if there wasn't a match between the main input and the lookup, then the output row would be populated with null-- At least that's the way it has been working.
So in this case, I don't see why the condition I've put before the lookup field in the output (!(GetInvoiceCost.eqnum == null) && !(GetInvoiceCost.location == null) ? GetInvoiceCost.eqnum : ) would affect that.
Sorry, but I'm confused now.
Thanks
One Star

Re: Using left outer join in tMap with other conditions

Bonjour,
J'analyse et je fais des tests sur plusieurs ETL ce qui m'oblige à faire des jointures.
J'ai un problème sur outer join avec le tMap.
J'aimerais faire une jointure de 3tables :
table1:
ida idb valeur1
1 1 200
1 2 300
2 1 40
table2:
ida idb valeur2
1 1 20
1 2 400
table3:
ida idb valeur3
1 1 150
2 1 90
Résultat que je veux :
ida idb valeur1 valeur2 valeur3
1 1 200 20 150
1 2 300 400 null
2 1 40 null 90
Comment puis je faire avec talend?
Si je joins les clés de la première table aux autres dans le tMap je n'ai pas les null j'ai que les résultats ou il y a la valeur1, la valeur2 et la valeur3 non null.
Merci,
Hélène