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.
@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 ?
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.
@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.
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?
@aporwal,if you configured correctly that innerjoin, yes when you the problem with data you will get,so you need to check the data.
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.
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.