topic Re: tMap OR operator in JOIN in Talend Studio

tMap OR operator in JOIN

jimbo20814 — Sat, 16 Nov 2024 03:19:56 GMT

All, in tMap how can I perform what's equivalent to a SQL OR within a JOIN? For example see below.

SELECT * FROM MainFlow mf

JOIN LookUp lu ON mf.colA = lu.colA AND (mf.colB = lu.colB OR mf.colC = lu.colC)

I tried to use a expression filter but does not do the job.

mf.colB== lu.colB || mf.colC == lu.ColC

Re: tMap OR operator in JOIN

cmendels — Fri, 07 Feb 2020 15:31:59 GMT

Did you place the expression in the filter on your lookup table or on your output?

Re: tMap OR operator in JOIN

jimbo20814 — Fri, 07 Feb 2020 15:33:59 GMT

I placed on the lookup table. Do I need to place on output?

Re: tMap OR operator in JOIN

cmendels — Fri, 07 Feb 2020 15:35:51 GMT

I would try it on the output to see if you get the results you want.

Re: tMap OR operator in JOIN

jimbo20814 — Fri, 07 Feb 2020 15:42:37 GMT

Actually, no that won't work/make sense because it's really a part of the JOIN between MainFlow and LookUp, once it's the output it's too late. Hmm, I figure this is a common task/requirement, an OR in a JOIN.

Re: tMap OR operator in JOIN

Anonymous — Fri, 07 Feb 2020 16:36:54 GMT

This depends on what you want. If both conditions in the OR match, I assume you only want the 1 row returned? If so, this can be done with a combination of tMap variables and output filters. I'll demonstrate a REALLY simplified version of how to do this in a tMap below.....

In this example I have two datasets, both with columns a, b and c. The join is on columns and b OR c. I do not join the input tables, which creates a cartesian join scenario. I then create two tMap variables of type boolean. I compare row1.b with row2.b to see if it matches. I also compare row1.c with row2.c to see if that matches. In the output table I apply a filter which uses the two tMap variables. If either of them are true, the row is returned.

Re: tMap OR operator in JOIN

jimbo20814 — Fri, 07 Feb 2020 17:24:39 GMT

Bingo! thanks so much rhall_2_0.

Re: tMap OR operator in JOIN

Anonymous — Sun, 09 Feb 2020 20:16:08 GMT

No problem, I'm glad it helped 🙂