topic Re: Trouble with inner join using tmap in Talend Studio

Trouble with inner join using tmap

Anonymous — Wed, 20 Jun 2018 07:20:54 GMT

I am facing a case with inner join over two mssql tables name T1(main) and T2 (lookup). When T2 is having no data at all, the inner join reject works perfectly and goes to the reject output . But when T2 has some rows, say 40 and T1 is having 100 rows, all 100 rows are going in the main output and reject is being blank, instead of (40 main + 60 reject). So, it works as left outer join in case T2 has some data.

Has anyone faced the same issue and help me in resolving this?

Note: My main and reject output tables are same as the T2 table with different entries defined in the tmap.

Re: Trouble with inner join using tmap

manodwhb — Wed, 20 Jun 2018 07:33:23 GMT

@aporwal,can you show the tMap settings for Not working with data?

you need to look in two ways that tMap join column and are you have duplicate data on join key ?

Re: Trouble with inner join using tmap

Anonymous — Wed, 20 Jun 2018 07:41:34 GMT

Hi Manohar,

The join key is unique for each row and defined so in table schema.

Additionally, I have tried printing output with tlogrow instead of T2 table and it is behaving in the same way.

I have attached the tmap setting screenshot.

Ankur

Re: Trouble with inner join using tmap

manodwhb — Wed, 20 Jun 2018 07:44:16 GMT

@aporwal,i do not see tMap configuration where you attached screenshot.

Re: Trouble with inner join using tmap

Anonymous — Wed, 20 Jun 2018 07:45:58 GMT

I attached in the original Post with other attachments.

Please check now.

Re: Trouble with inner join using tmap

manodwhb — Wed, 20 Jun 2018 07:46:58 GMT

@aporwal,which version of Talend,are you using?

Re: Trouble with inner join using tmap

Anonymous — Wed, 20 Jun 2018 07:51:21 GMT

Version is 6.5.1.

Re: Trouble with inner join using tmap

manodwhb — Wed, 20 Jun 2018 07:54:25 GMT

@aporwal,i do not see any issue with Version is 6.5.1,it could be tMap configuration or data may be matching with main and lookup table.

so please check configuration of tMap and data.

Re: Trouble with inner join using tmap

Anonymous — Wed, 20 Jun 2018 08:00:53 GMT

Manohar,

I have attached tmap screenshot, is there any problem in that, considering inner join and rejects?

When Main table is having 800 rows, and lookup has 154 rows, how can inner join show 800 rows in main output? It is questioning the basic definition of inner join.

Running the same query in sql server gives the correct 154 rows for inner join.

Do you see such problem with inner join in community edition in general?

Re: Trouble with inner join using tmap

manodwhb — Wed, 20 Jun 2018 08:11:07 GMT

@aporwal,if you configured correctly that innerjoin, yes when you the problem with data you will get,so you need to check the data.

Re: Trouble with inner join using tmap

Anonymous — Wed, 20 Jun 2018 08:15:15 GMT

Same query working with SSMS well, seems to me as no problem with data.

Could you suggest anything else to check?

Re: Trouble with inner join using tmap

ThWabi — Wed, 20 Jun 2018 13:00:00 GMT

Hello aporwal,

in your tMap component on the output side, move the inner join reject output "out3" to the top so that it is "order:1" of the outputs.

Best regards,

Thomas

Re: Trouble with inner join using tmap

manodwhb — Wed, 20 Jun 2018 13:09:40 GMT

@ThWabi, how the order of tMap output sections will make differences?

Re: Trouble with inner join using tmap

ThWabi — Wed, 20 Jun 2018 14:04:48 GMT

Hi Manohar,

as far as I can see from the tMap screenshot, the outputs do not have any data from the lookup. So I thought it might make a difference whether the reject output is processed first or second. My apologies if this is wrong.

Best regards,

Thomas