One Star

tMap filter expressions not working as expected - simple filter

I have a simple job that consists of two tMysqlInputs and a tMap. I use tLogRow to capture the flow for debugging purposes.
Table A, parent table:
|=-------+------=|
|test1_id|test1_a|
|=-------+------=|
|1 |1aa |
|2 |2aa |
|3 |3aa |
|4 |4aa |
|5 |5aa |
|6 |6aa |
|7 |7aa |
'--------+-------'
Table B, lookup table:
|=-------+------=|
|test2_id|test2_a|
|=-------+------=|
|1 |1az |
|2 |2aa |
|3 |3az |
|4 |4aa |
|5 |5az |
|8 |8az |
|9 |9az |
'--------+-------'
I join in tMap where test1.test1_id = test2.test2_id. This gives the expected output as shown below. The last two records of the parent and look up tables do not appear since I'm doing an inner join. This is as expected. So far so good.
|=-------+-------+--------+------=|
|test1_id|test1_a|test2_id|test2_a|
|=-------+-------+--------+------=|
|1 |1aa |1 |1az |
|2 |2aa |2 |2aa |
|3 |3aa |3 |3az |
|4 |4aa |4 |4aa |
|5 |5aa |5 |5az |
'--------+-------+--------+-------'
So now I add a filter expression in the tMap control:
Parent.test1_a != Child.test2_a
And I run it but I get the same results:
|=-------+-------+--------+------=|
|test1_id|test1_a|test2_id|test2_a|
|=-------+-------+--------+------=|
|1 |1aa |1 |1az |
|2 |2aa |2 |2aa |
|3 |3aa |3 |3az |
|4 |4aa |4 |4aa |
|5 |5aa |5 |5az |
'--------+-------+--------+-------'
If I change the filter expression to:
Parent.test1_a == Child.test2_a
I get no results.
What am I missing? The tMap component seems to think that test1_a does not equal test2_a in any case, but you can see from the data that the second and fourth row actually have matches.
What should I have done to filter the two inputs to only output rows where test1.test1_a equals test2_test2.a?
2 REPLIES
One Star

Re: tMap filter expressions not working as expected - simple filter

I think it's a cast error since you're using == which is usually designed to compare integers.
I would try: Parent.test1_a.equals(Child.test2_a)
Assuming you're using Java, the above should work - I did something similar myself.
One Star

Re: tMap filter expressions not working as expected - simple filter

You are right in that it works as expected with integers. I'm doing this in the expression builder in the tMap control, but I didn't realize it expected the filter expressions to be java expressions. I was writing the expression as if it were part of a sql statement.
Thanks - what you wrote works great!