One Star

Implementing complex join

Hi!
I'm pretty new with talend and I hit a problem. I dont see how I can implement the solution so here it goes...
In a normal lookup, you left join on a key and return several columns. To acheive my goal, i've to do the exact opposite : join on several non key column and return the key if there is a match. In other word, I want to find a pattern an extract the key if it exist. On one side you have several transactions (with several properties) to process. On the other you have several transaction pattern (with several propeties) and the goal is to find one patten that fit each transactions.
The SQL to accheive this is fairly simple:
select t.fk_transaction_code,
s.fk_system,
s.fk_customer,
s.fk_partner
from source.v_mapping_source s
left join ods.v_mapping_target t on (s.fk_system = t.fk_system OR t.fk_system = 0)
and (s.fk_function = t.fk_function OR t.fk_function = 0)
and (s.fk_customer = t.fk_netsuite_customer OR t.fk_netsuite_customer = 0)
and (s.fk_partner = t.fk_netsuite_partner OR t.fk_netsuite_partner = 0)

-The left join itself ensure a result for all row in the source
- The OR on each part of the join ensure that if a match is not found, use the default value for the property (wich is zero).
If I put in my join something like:
       left join ods.v_mapping_target t on s.fk_system   = t.fk_system
and s.fk_function = t.fk_function
and s.fk_customer = t.fk_netsuite_customer
and s.fk_partner = t.fk_netsuite_partner

I would only hit a result when all 4 propeties were set. the OR allow me to hit a result if one or more property is not set.
5 REPLIES
One Star

Re: Implementing complex join

My question is how to modify my tmap to hendle these OR clause in the lef join.
I'm going to look for the proper way to add an image of my tmap.
One Star

Re: Implementing complex join

My question is how to modify my tmap to hendle these OR clause in the lef join.
I'm going to look for the proper way to add an image of my tmap.

I put a red square in SQL would be :
row6.fk_partner = row17.fk_netsuite_partner.

What should I change in the tmap to have
row6.fk_partner = row17.fk_netsuite_partner OR row17.fk_netsuite_partner = 0

instead?
Any help would be appreciated.
Sorry for my english. Not my first language!

Re: Implementing complex join

one not-so elegant solution would be to do the join without the OR in the tmap, and then in the inner join reject output table apply an output filter that checks for the zero values. you can then recombine the flows or simply use two different components to do all your inserts.
Seven Stars

Re: Implementing complex join

Instead of joining those fields directly in the lookup (e.g. your red square), activate the expression filter for the lookup (white arrow with green plus) with the following:
(row6.fk_system==row17.fk_system || row17.fk_system==0)
&& (row6.fk_function==row17.fk_function || row17.fk_function==0)
&& (row6.fk_customer==row17.fk_netsuite_customer || row17.fk_netsuite_customer==0)
&& (row6.fk_partner==row17.fk_netsuite_partner || row17.fk_netsuite_partner==0)
One Star

Re: Implementing complex join

This is great. With this solution, you maid me understand so much stuff about the tmap.
Thanks a lot!!!