One Star

[resolved] Multiple condition join on tMap ?

Hi,
I'd like to make a multiple condition join with a tMap. I have an input source data which contains 2 possibles values for a bar code. I have the same structure in a lookup data table.
I'd like to join the two when one of the main data match one of the lookup data.
In SQL that would be something like : left join table B ON (A.bar1=B.bar1 or A.bar1=B.bar2 or A.bar2=B.bar1 or A.bar2=B.bar2)
Thanks
1 ACCEPTED SOLUTION

Accepted Solutions
Seven Stars

Re: [resolved] Multiple condition join on tMap ?

The way your tMap is set up there are no rejects. Rejects can either be from the inner-join or from the output table filters but you have not used either.
With only an expression filter on the lookup (no values against any of the lookup table's fields), the first output table will have every row in the lookup table for every row in the main table but will null the lookup values if the expression is false. Moving the expression to the output table filter will still mean you get every lookup row for every main row but they will be split between the output tables.
The only ways to get tMap to give you an output table of those main rows that have no match in the lookup using any combination of the fields is to either:
1. have four separate lookups with the different left-joins and an appropriate output filter, but this will not give you multiple rows from the lookup table if there are multiple matches from the main table
2. duplicate both the main and lookup tables before tMap so there are two rows with new different lookup keys in addition to the two original columns, do the inner-join in tMap and then deduplicate the outputs after tMap
As Lijo suggested, it's probably better to just do the join in your SQL.
6 REPLIES
One Star mpa
One Star

Re: [resolved] Multiple condition join on tMap ?

Hi,
I think you just have to add an expression where you put your (A.bar1=B.bar1 or A.bar1=B.bar2 or A.bar2=B.bar1 or A.bar2=B.bar2).
Then you should disable "inner join" and then you should have all your records from your main data.
The ones that didn't have a matching record in the lookup will have "null" in the columns received from the lookup data stream.
I hope this helps,
Regards
One Star

Re: [resolved] Multiple condition join on tMap ?

Thanks for your reply, this is exactly what I tried without success before posting here.
I attached 2 screen dumps.
on the first you can see how things are set up.
on the second, all 3 of my main input data are going to the reject table when all the bar code are defined in the lookup table.
any help appreciated
One Star

Re: [resolved] Multiple condition join on tMap ?

correction, it is not even going to the reject table. It seems like it fails matching all together
One Star

Re: [resolved] Multiple condition join on tMap ?

Hi
First of all for string comparison use A.bar1.equals(B.bar1)
Your job will be slow if you give only expression as this is not jain any more, its filter.
If all the data is there is same database i would prefer you to use ELT components.
Seven Stars

Re: [resolved] Multiple condition join on tMap ?

The way your tMap is set up there are no rejects. Rejects can either be from the inner-join or from the output table filters but you have not used either.
With only an expression filter on the lookup (no values against any of the lookup table's fields), the first output table will have every row in the lookup table for every row in the main table but will null the lookup values if the expression is false. Moving the expression to the output table filter will still mean you get every lookup row for every main row but they will be split between the output tables.
The only ways to get tMap to give you an output table of those main rows that have no match in the lookup using any combination of the fields is to either:
1. have four separate lookups with the different left-joins and an appropriate output filter, but this will not give you multiple rows from the lookup table if there are multiple matches from the main table
2. duplicate both the main and lookup tables before tMap so there are two rows with new different lookup keys in addition to the two original columns, do the inner-join in tMap and then deduplicate the outputs after tMap
As Lijo suggested, it's probably better to just do the join in your SQL.
One Star

Re: [resolved] Multiple condition join on tMap ?

Thanks for your answers.
I'll pass on using duplicate main and lookup tables as they are more than a couple of million of records each.
I cannot use the ELT component either because I need to use a 'OR' in my join clause and it can only take a 'AND'.
I have therefore created the appropriate SQL request in the repository and used it as my input data. The only trouble with that solution is that I need to handle the non matching data myself !