Two Stars

Trouble with inner join using tmap

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. 

13 REPLIES
Twelve Stars

Re: Trouble with inner join using tmap

@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 ? 

Manohar B
Two Stars

Re: Trouble with inner join using tmap

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

Twelve Stars

Re: Trouble with inner join using tmap

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

Manohar B
Two Stars

Re: Trouble with inner join using tmap

I attached in the original Post with other attachments.

Please check now.

Twelve Stars

Re: Trouble with inner join using tmap

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

Manohar B
Two Stars

Re: Trouble with inner join using tmap

Version is 6.5.1.

Twelve Stars

Re: Trouble with inner join using tmap

@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.

Manohar B
Two Stars

Re: Trouble with inner join using tmap

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? 

Twelve Stars

Re: Trouble with inner join using tmap

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

Manohar B
Two Stars

Re: Trouble with inner join using tmap

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

Could you suggest anything else to check?

Six Stars

Re: Trouble with inner join using tmap

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

 

Twelve Stars

Re: Trouble with inner join using tmap

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

Manohar B
Six Stars

Re: Trouble with inner join using tmap

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