topic Re: TMap Left Outer Join With multiple OR conditions in Talend Studio

TMap Left Outer Join With multiple OR conditions

MohleySingh — Wed, 17 Apr 2019 02:29:20 GMT

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 !

Re: TMap Left Outer Join With multiple OR conditions

Anonymous — Wed, 17 Apr 2019 04:29:56 GMT

Hello,

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

Please have a look at my screenshot

Best regards

Sabrina

Re: TMap Left Outer Join With multiple OR conditions

MohleySingh — Wed, 17 Apr 2019 04:36:20 GMT

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.

Re: TMap Left Outer Join With multiple OR conditions

Anonymous — Wed, 17 Apr 2019 07:42:39 GMT

Hello,

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

Best regards

Sabrina

Re: TMap Left Outer Join With multiple OR conditions

MohleySingh — Wed, 17 Apr 2019 07:51:21 GMT

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

Re: TMap Left Outer Join With multiple OR conditions

sbxr — Wed, 17 Apr 2019 09:21:15 GMT

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

Re: TMap Left Outer Join With multiple OR conditions

MohleySingh — Thu, 18 Apr 2019 05:33:27 GMT

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