hi I am very new to talend. I want to have inner join between two tables with case insensitive condition. can I force the join between two tables on a column such that match returns true even if the data in two tables are in different case. Any help on this is very much appriciated.
Hi Shong
This will work only when all the data for column (lowername) in out2 table are in
in lowercase. My problem is that some data arein lower case and some are in upper case and
some even in mixed case.
I want to match for equalsIgnoreCase condition between the tables out2 and row1.
Hello vk_sang
My job works for all case, contains lowercase, upper case and mixed case. I convert all case to lower case and compare them.
Best regards
Hi Shong;
If i have data in one table all in lower case and data in another table in mixed case then there is no problem; However problem is that in one table there are duplicate email addresses in different cases and in another table there are unique email address but it can be in any case for the same user. Now I have to do an inner join between the main table which contains duplicate emails and look up table which contains unique email address. So i cant convert them to any specific case. Only solution is if i can have something which can compare the email in two tables with equalIgnoreCase feature the way we do in java it should work and I am not able to find any component in Talend which it can do it for me.
Regards
VK
I just came across the same problem - lookups on tMaps are case sensitive. The solution I came up with was to insert a new tMap on one of the lookup tables, with an "uc()" transformation on each of the fields as it got mapped from input to output. You can then join these upper case inputs with uc() matches on the other table. Ugyl but it works. (this is kind of unfortunate - the default collating sequence for mySQL is case insensitive, however the lookups in tMap are case sensitive, which caused me to scratch my head for a long time trying to determine why Talend wasn't doing what I thought it should for tMap lookups.)
Shong,
Can you provide an image of the actual job for doing this?
I am running into the same issue where my Incoming data is camelCased, and the data in MySQL is mixed between all upper, first letter upper, all lower, camelCased, etc...
How do i get this running so that the Inner Join match is case insensitive?
I have 8 tables that join the incoming data.
Thanks.
Shong's answer works. I think what you're missing is image 1 does a mapping ftom tMap1 tto tMap2 (mixed case to lowercase). The output from tMap1 is the lookup in tMap2 and the main file to tMap2 has the lowercase function applied to the mixes case field for the match
I think i have this working, but is there a way to filter out or ignore incoming records that 'null' in the field i am joining on? It seems that if the field that I am trying to join on contains 'null', i get a 'java.lang.NullPointerException' error. Thanks.
