topic Re: How to perform non equi join with talend in Talend Studio

How to perform non equi join with talend

vboppudi — Tue, 07 Nov 2017 22:37:31 GMT

Hi All,

I want to join two tables in talend based on <= join condition. How to achive this.

Ex:

SRC1

ID NAME

4 ABC

2 XYZ

SRC2

ID LOC

1 W

2 E

3 R

4 T

My output should be as below

TGT

ID NAME LOC

4 ABC W

4 ABC E

4 ABC R

4 ABC T

2 XYZ W

2 XYZ E

Please let me know if further details required.

Regards,

Re: How to perform non equi join with talend

Anonymous — Wed, 08 Nov 2017 01:00:05 GMT

An easy way to achieve this is to not join the Main to the Lookup in the tMap and just filter the output. This will essentially create a cartesian join and you filter the output table. Take a look at the image which shows how I have done this....

Re: How to perform non equi join with talend

Anonymous — Fri, 10 Nov 2017 09:38:26 GMT

Did this resolve your question? If so can you set it to resolved so that others can benefit from this?

Re: How to perform non equi join with talend

vboppudi — Mon, 13 Nov 2017 22:35:16 GMT

Hi What if i have thousands of records in other table and need to join only 3 or 4 records from main table. still need to do cross join? how about performance?

Regards,

Re: How to perform non equi join with talend

Anonymous — Mon, 13 Nov 2017 23:51:49 GMT

Performance of a cross join is always going to be poor. But you can mitigate for that by dynamically modifying the WHERE the clause of your lookup query with values from your main query (if, as you say, you only want a few values). This is very simple using Talend. You simply need to build a dynamic WHERE clause from the datarows returned from your MAIN query before it is passed to the tMap, then use that WHERE clause in your lookup.