TMap Left Outer Join With multiple OR conditions

[MohleySingh]

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 !

[Anonymous]

Hello,

You can add the condition in expression section of output tmap.

Please have a look at my screenshot

Best regards

Sabrina

[MohleySingh]

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.

[Anonymous]

Hello,

Could you please elaborate your case with an example with input and expected output values?

Best regards

Sabrina

[MohleySingh]

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

[sbxr]

Hi @Moe   which column is your key .You must define a key for this and what is your match type in tmap.

[MohleySingh]

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