Hi All I have a doubt about the inner join in tMAP: I am doing a inner join between 2 tables, i have activated the "catch inner join lookup reject" in the output for rejects rows. Does the reject flow collects: - ONLY all rows in A table but not in B table ? - OR all rows in A table but not in B table PLUS all rows in B table but not in A table ? Thank you for the answer Regards
Hi kzone, thank you for your reply but i am afraid i didn't get you. i have 2 tables A and B, how can i get A-B and B-A using INNER JOIN with reject option ? I know that this is possible using 2 subjobs with left outer join(1st table A=main, B=lookup, 2nd vice versa) but i want to avoid doing 2 subjobs. thank you
tMap components have the concept of a Main row and Lookup rows. The Main row is key here. If we say A table is connected as the Main row, then the tMap is driven by that table. What you are doing here is essentially a left outer join where you are catching the rows that match from your Main and Lookup as your "A=B" output, and the rows that do not match (but only from the Main) as your "A-B" output. To get B-A rows, you will need to compare the output of A=B with B and do B-(A=B). I believe the reason behind not having a full outer join or a right outer join is that a flow is driven by the data of the first component in that flow. If this is not strictly adhered to then data lineage can become tricky. Enabling two driving forces in the tMap components (more than 1 Main input), how would you know where the data originated? The other way of doing this if you don't want to litter your Job with lots of tMaps is to perform this join in your source database (put the join logic into your DB input component). Ofcourse this does require that the data is from the same DB or that DB links are allowed. If you data is from the same DB I find joining and filtering before I get the data into Talend is the most efficient approach. In this situation you can do the calculation before you bring the data into Talend and use some sort of indicator column to identify A-B, B-A or A=B.