Hi !
I searched the forum first, but did not find an answer. It seems that in tMap, when performing a left outer join, the filter of the lookup table is not considered. Which means that I end up with way too many records in the resulting output. More precisely, I have the following table in the main flow :
id, space, position
1, X, 910845
2, X, 467963
3, X, 1919947
4, Y, 467963
and the following table for the lookup flow :
id, space, coord1, coord2
1, X, 867096, 894061
2, X, 927586, 973659
3, X, 469127, 517230
and I want to match all entry in the main flow for which the position falls between lookup flow coord1 - 20000 and lookup flow coord2 + 20000 and which belong to the same space. In SQL, my query is :
select * from main left join lookup on main.space=lookup.space and (lookup.coord1 - main.position) < 20000 and (main.position - lookup.coord2) < 20000;
in tMap, I generated a key between fields main.space and lookup.space, but I used a filter for the rest, since it is not one-to-one field correspondance.
Executing the above SQL yields :
1, X, 910845, 1, X, 867096, 894061
1, X, 910845, 2, X, 927586, 973659
2, X, 467963, 3, X, 469127, 517230
3, X, 1919947,,,,
4, Y, 467963,,,,
and I should get the same out of tMap, but instead I get :
1, X, 910845, 1, X, 867096, 894061
1, X, 910845, 2, X, 927586, 973659
1, X, 910845,,,,
2, X, 467963,,,,
2, X, 467963,,,,
2, X, 467963, 3, X, 469127, 517230
3, X, 1919947,,,,
3, X, 1919947,,,,
3, X, 1919947,,,,
4, Y, 467963,,,,
Which is incorrect. Ok, I do get populated fields corresponding to lookup only for the "right" corresponding main records (and empty fields otherwise), but I get too many total records. In fact I get the same records as when I execute my previous SQL statement with a join condition on space only :
select * from main left join lookup on main.space=lookup.space ;
So, finally, the question is : what am I missing ? How can I get tMap to give me only the same 5 records I get with SQL ?
Thanks !