topic Re: inner join on two tables with case insensitive in Talend Studio

inner join on two tables with case insensitive

Anonymous — Sat, 16 Nov 2024 13:47:53 GMT

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.

Re: inner join on two tables with case insensitive

Anonymous — Fri, 28 Aug 2009 04:21:44 GMT

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

shong

Re: inner join on two tables with case insensitive

Anonymous — Fri, 28 Aug 2009 17:35:46 GMT

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.

Re: inner join on two tables with case insensitive

Anonymous — Sun, 30 Aug 2009 03:22:58 GMT

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?

Re: inner join on two tables with case insensitive

Anonymous — Mon, 31 Aug 2009 03:41:40 GMT

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

Re: inner join on two tables with case insensitive

Anonymous — Tue, 01 Sep 2009 10:00:49 GMT

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

Re: inner join on two tables with case insensitive

Anonymous — Tue, 06 Oct 2009 00:38:35 GMT

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

Re: inner join on two tables with case insensitive

Anonymous — Wed, 16 Feb 2011 19:04:49 GMT

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.

Re: inner join on two tables with case insensitive

janhess — Thu, 17 Feb 2011 10:00:15 GMT

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

Re: inner join on two tables with case insensitive

Anonymous — Fri, 18 Feb 2011 16:10:25 GMT

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.

Re: inner join on two tables with case insensitive

janhess — Fri, 18 Feb 2011 16:13:35 GMT

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

Re: inner join on two tables with case insensitive

Anonymous — Fri, 18 Feb 2011 17:34:10 GMT

Re: inner join on two tables with case insensitive

janhess — Mon, 21 Feb 2011 09:28:16 GMT

In that case don't select the inner join.

Re: inner join on two tables with case insensitive

Anonymous — Wed, 15 May 2013 12:14:20 GMT

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

Re: inner join on two tables with case insensitive

janhess — Wed, 15 May 2013 14:36:56 GMT

You could try posting a new topic.

Re: inner join on two tables with case insensitive

Anonymous — Thu, 16 May 2013 04:53:46 GMT

Hi vk_sang,
Perhaps this related forum will help you
https://community.talend.com/t5/Design-and-Development/Copy-a-database/td-p/106219.
Best regards
Sabrina

Re: inner join on two tables with case insensitive

Anonymous — Fri, 27 Jan 2017 10:21:48 GMT

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.

Re: inner join on two tables with case insensitive

Anonymous — Fri, 27 Jan 2017 13:27:27 GMT

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.

Re: inner join on two tables with case insensitive

Anonymous — Tue, 16 Oct 2018 11:14:34 GMT

Hi Shong,

I am not able to see the screenshots please help.

Regards
Saiba

Re: inner join on two tables with case insensitive

Niki1 — Thu, 11 Jul 2019 08:23:31 GMT

Can you pls share the screenshot as a solution to this issue plss