Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
How to do Left Outer Join in TMap with Multiple OR conditions.
For Example I want simulate this in TMap:
Select T1.*, T2.*
from T1 left outer join T2
on t1.col1 = t2.col1 OR t1.col2 = t2.col2
Thank you in advance for your help !
Hello,
You can add the condition in expression section of output tmap.
Please have a look at my screenshot
Best regards
Sabrina
Thanks for your reply Sabrina,
I got what you mean. But in that case I will have to make lookup match model as "all rows" and the output will be a cross join and the output expression will filter any non matching rows.
What I need is left outer join so that I can still process the rows from "main"(in your example row1 ) in case there is no valid match for any rows.
Hello,
Could you please elaborate your case with an example with input and expected output values?
Best regards
Sabrina
Hi Sabrina,
Below is the sample input and output I am expecting from TMap:
Main Input | |
Row1 | |
Col1 | Col2 |
1 | a |
2 | b |
3 | c |
Lookup | |
Row2 | |
Col1 | Col2 |
11 | a |
2 | bb |
Output | |||
Row1.Col1 | Row1.Col2 | Row2.Col1 | Row2.Col2 |
1 | a | 11 | a |
2 | b | 2 | bb |
3 | c | null | null |
Hi @Moe which column is your key .You must define a key for this and what is your match type in tmap.
Hey Sbxr,
Sorry for delay in reply. I am flexible to keep any match model as long as it solves my problem.
I am not sure why we need keys to get the solution. Just to make clear what I am trying to achieve:
I need to implement equivalent of below query in Talend TMap:
Select T1.*, T2.*
from T1 left outer join T2
on t1.col1 = t2.col1 OR t1.col2 = t2.col2
Below is the sample input and output I am expecting from TMap:
Main Input Table | |
T1 | |
Col1 | Col2 |
1 | a |
2 | b |
3 | c |
Lookup Table | |
T2 | |
Col1 | Col2 |
11 | a |
2 | bb |
Output | |||
T1.Col1 | T1.Col2 | T2.Col1 | T2.Col2 |
1 | a | 11 | a |
2 | b | 2 | bb |
3 | c | null | null |