One Star

inner join on two tables with case insensitive

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.
17 REPLIES
Community Manager

Re: inner join on two tables with case insensitive

Hello guy
Before you join the two column, you need convert the lookup column to lowercase or uppercase, see my screenshots.
Best regards

shong
----------------------------------------------------------
Talend | Data Agility for Modern Business
One Star

Re: inner join on two tables with case insensitive

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.
One Star

Re: inner join on two tables with case insensitive

Looks like you would have to do a transform to your field before the tMap to make it a fixed case and then do the tMap. Is this correct?
Community Manager

Re: inner join on two tables with case insensitive

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

shong
----------------------------------------------------------
Talend | Data Agility for Modern Business
One Star

Re: inner join on two tables with case insensitive

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
One Star

Re: inner join on two tables with case insensitive

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.)
One Star

Re: inner join on two tables with case insensitive

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.
One Star

Re: inner join on two tables with case insensitive

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
One Star

Re: inner join on two tables with case insensitive

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.
One Star

Re: inner join on two tables with case insensitive

You could use the expression filter on the input row to ignore nulls.
One Star

Re: inner join on two tables with case insensitive

janhess:
Thanks for the follow up.
I had tried that, but it doesnt seem to be sticking. Here's more info on my situation:
I have 1 excel file pulling in the records.
there are 3 columns with string based values that are values of a record in one of 3 tables of lists.
excel file:
id | make | drive | color
1 | Honda | AWD | red
2 | HonDa | | blue
3 | Dodge | Rwd | Black
4 | Dodge | 4x4 |
Tables:
Make:
1 | Honda
2 | DODGE
Drive:
1 | awd
2 | RWD
3 | 4X4
Color:
1 | Green
2 | RED
3 | BlacK
4 | BLUE
I need to pull in all records, no matter the case. This part seems to be working.
I am getting hung up I think when there is a missing attribute that is actually blank on the incoming rows such as :
id | make | drive | color
2 | HonDa | | blue (which should end up in the reciving table as: 2 | 1 | null | 4)
4 | Dodge | 4x4 | (which should end up in the reciving table as: 4 | 2 | 3 | null)
for these two records, for record #2, drive should just end up being 'null' when it gets inserted into the receiving table
and Color should be 'null' as well.
I added !Relational.ISNULL(incoming.drive) to the filter of initial tMap where I was doing the 'toLowerCase', but that didnt work. When I added it to the main tMap, I am getting an error there as well.
Is there a better/more accurate expression/location that I should be adding these?
thanks again for any / all help you can provide.
One Star

Re: inner join on two tables with case insensitive

In that case don't select the inner join.
One Star

Re: inner join on two tables with case insensitive

Hi
I have some 100 tables in one database on one server and I want to copy all the tables along with the all the data to a different database on another server. How can I do this bulk migration.
Any help is much appreciated
One Star

Re: inner join on two tables with case insensitive

You could try posting a new topic.
Moderator

Re: inner join on two tables with case insensitive

Hi vk_sang,
Perhaps this related forum will help you
Forum 18572.
Best regards
Sabrina
--
Don't forget to give kudos when a reply is helpful and click Accept the solution when you think you're good with it.
One Star

Re: inner join on two tables with case insensitive

Hello,
I am trying to do the same as in the initial post, however I do not see the screenshots as indicated in the first (shong's) response. Would someone please repost images, or explain steps to get lookup column in lowercase in order to do the join?
Thanks in advance.
One Star

Re: inner join on two tables with case insensitive

I don't know if this was how it was done, but one way I got it done was using another tMap to feed into another.