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.