topic Re: [resolved] upper case problem in Talend Studio

[resolved] upper case problem

Anonymous — Sat, 16 Nov 2024 12:29:10 GMT

i m doing an inner join on 2 columns of 2 tables
Values in both the columns are in upper case and lower case
so i want to convert both the columns to uppercase and then compare them.
How do i do this, because
i have a tMap and i have defined an inner join on those 2 columns say
table A
col11
table B
col 12

inner join is in row1.col11 and row2.col12
and my expression in row2 is
StringHandling.UPCASE(row1.col11) | col12
and in expression of row2 i have StringHandling.UPCASE(row2.col12)
when i run this i get error as
The operator ! is undefined for argument type(s) String
But i m not using ! operator anywhere! so why is this error ?
Is there a way where i can compare 2 columns by ignoring the case.

Re: [resolved] upper case problem

Anonymous — Fri, 30 Dec 2011 08:57:28 GMT

Hi
The description of this error may be not exact here.
But setting expressions of row1,row2 is not a good way.
The value type of expression needs to be boolean.
StringHandling.UPCASE(row1.col11) is still a String.
In Talend Java code, it is like this.

!(
StringHandling.UPCASE(row2.col12)
)

Then you get a ! operator error.
Here is a workaround.
Best regards!
Pedro

Re: [resolved] upper case problem

Anonymous — Fri, 30 Dec 2011 09:25:48 GMT

Thanx for the help Pedro.
For all the tutorials i have seen to have join i see that they simply drag n drop the columns join.Here internally what is happening is what you shown in the example i.e UPCASE(....).equals(UPCASE(....));
am i right ?
So then do i still have to mention an inner join in Property --> Join Model ?
Now say suppose i have two conditions to join two tables.
i.e firstly :- tableA.col11 = tableB.col12(inner join)
and TableB.col13 = 150
How do i get this ?
I tried to enter in the expression of tableB as row2.col13 = 150
Please help..i could get confused here !

Re: [resolved] upper case problem

Anonymous — Fri, 30 Dec 2011 09:38:47 GMT

Hi
UPCASE(....).equals(UPCASE(....)) is just a workaround about this issue.
The normal way is 'inner join' which is recommended.
The expression of row2 is a filter indeed which will excute before inner join.
You may set tMap as follows.
Step 1: set expression of row2: row2.col13==150
Step 2: Join Model-'inner join'.
Best regards!
Pedro

Re: [resolved] upper case problem

Anonymous — Fri, 30 Dec 2011 10:03:57 GMT

Thanx for your reply.
I appreciate the help.

Re: [resolved] upper case problem

Anonymous — Fri, 30 Dec 2011 13:03:26 GMT

Hi,
I would also like to know.
That the work around works fine.
But then ehat to do in case
where i have to put an innerjoin as as i have to get rejected rows and also want to convert to uppercase and compare and then i have an extra join condition ?

Re: [resolved] upper case problem

Anonymous — Sat, 31 Dec 2011 02:03:05 GMT

Hi
That's why 'inner join' is recommended. Because it can get the 'reject' result derectly in tMap.
You have used UPCASE(....).equals(UPCASE(....)) and want to get rejected rows.
I'm afraid you have to use two tMaps to get 'reject'. Compare all records in Table1 with the rows after convertion in first tMap.
Best regards!
Pedro

Re: [resolved] upper case problem

Anonymous — Sat, 31 Dec 2011 06:07:11 GMT

Thanx for the reply Pedro!
Wish you a happy new Year.