tMap OR operator in JOIN

[jimbo20814]

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

[cmendels]

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

[jimbo20814]

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

[cmendels]

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

[jimbo20814]

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.

[Anonymous · Accepted solution]

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. 

[jimbo20814]

Bingo! thanks so much rhall_2_0.

[Anonymous]

No problem, I'm glad it helped 🙂