One Star

[resolved] tMap inner join rejects

Dear all,
I am trying to understand how to capture any records from my input file (row4) which could not be matched/joined successfully.
Despite reading the various topics I still don't understand ... perhaps I am a little slow but please help Smiley Happy
To further clarify:
Input record is say:
ID | Number | Text
1234|22222|sometext
1235|22221|sometext
1234 was successfully inserted into the database so fine
1235 however was not matched via the inner join and is to be written away into a new delimited file hence must be caught.
I am asking how to do this; I tried setting "catch lookup inner join rejects" however that's certainly not resulting in what I need as all records of the tables are logged and not the ones from the file.
1 ACCEPTED SOLUTION

Accepted Solutions
Seven Stars

Re: [resolved] tMap inner join rejects

Well you haven't done the second join in your screenshot so I'm not surprised you're not getting your expected results. Move row6 above row5 and make the join.
6 REPLIES

Re: [resolved] tMap inner join rejects

you are on the right track.
It sounds like what you need is two output tables-- one for data that joins, and one (with catch inner join rejects set) that shows you records from your main flow (row4) that would not join to your lookup (row5)
One Star

Re: [resolved] tMap inner join rejects

Hi John,
Thanks but I am still not getting there.... (I warned I may be a bit slow)...
Please advise on what's going so horribly wrong here... the file only got 5 rows as input and 3 match; 2 won't so basically only 2 rows should go to the rejected list ... not all those found in the database tables.
Really appreciate your (& others') help
- CPA
Seven Stars

Re: [resolved] tMap inner join rejects

Why is there no join between row4 and row5? It would help to know what's in the lookups and what results you are expecting and getting.
One Star

Re: [resolved] tMap inner join rejects

Hi Alevy,
Basically the row4.AccountID is joined onto row6.REF_NO; than the matching row6.ACCOUNTS1 is joined onto row5.ACCOUNTID; well that's what I would want it to be:
I.e.
INNER JOIN ROW6 ON ROW6.REF_NO = ROW4.ACCOUNTID
INNER JOIN ROW5 ON ROW5.ACCOUNTID = ROW6.ACCOUNTS1
Does that help?
Seven Stars

Re: [resolved] tMap inner join rejects

Well you haven't done the second join in your screenshot so I'm not surprised you're not getting your expected results. Move row6 above row5 and make the join.
One Star

Re: [resolved] tMap inner join rejects

Awesome that was the missing piece of knowledge ... thanks so much for this Smiley Happy