Hi,
I have a problem when grouping by a text column from databases with different character sets.
Database A: WE8ISO8859P15
Database B: WE8MSWIN1252
On both databases I have a similar table with a text column (desc). On DB A it's a char-type, on DB B it's nvarchar2. Both columns should contain the same text, but the last character is a reversed question mark (¿).
The strange thing is when I try to compare the columns, according to the INNER JOIN both columns are equal, but when I try to group by that column, it seems that they have different contents.
When i do a dump(desc) on both columns I get for the reversed question mark on DB A ASCII Code 133, and for DB B I get 191.
How I can I convert between the different character sets, so that my GROUP BY works as well?
Thanks for your help!
select * from
(SELECT to_char(descA) AS desc1 FROM A WHERE ID=1)a
INNER JOIN
(SELECT descB AS desc2 FROM B WHERE ID=1) b
ON a.desc1=b.desc2
WHERE a.desc1=bdesc2
--> 1 row (-->descA==descB)
SELECT desc, count(*)
(SELECT to_nchar(descA) desc FROM A WHERE ID=1
UNION ALL
SELECT descB AS desc FROM B WHERE ID=1)
GROUP BY desc
--> 2 rows (can't group by desc --> descA!=descB)