When writing queries, the error “Expressions with different collating sequences cannot be compared or concatenated” (error code -12637) may appear. What does this error mean and how can we correct it so that the query works as expected?
Let’s look at an example situation where we have two tables where the first table use english collation ordering and the other use swedish ordering:
SQL>create table a (c1 varchar(2) collate english_1);
SQL>insert into a values ('a');
SQL>create table b (c2 varchar(2) collate swedish_1);
SQL>insert into b values ('ä');
SQL>create view av as select * from a;
SQL>create view bv as select * from b;
SQL>select * from av join bv on (av.c1=bv.c2);
1: select * from av join bv on (av.c1=bv.c2)
^
Mimer SQL error -12637 in function PREPARE
Expressions with different collating sequences cannot be compared or concatenated
As shown, we attempt to join views av and bv using an equality condition on the c1 and c2 columns but the query fails. The reason is that the columns are defined to use different collations.
In this case the column c1 is collated using the english language and the column c2 is collated using swedish. Although similar, english and swedish collations differ in a few important ways. One such difference is the character ä. In the swedish language, ä is a distinct letter not equal to a, but in the english language, ä is just an a with an accent so they are regarded as equal.
By specifying which collation to use in the condition, we can make the query work.
SQL>select * from av join bv on (av.c1=bv.c2 collate english_1);
c1 c2
== ==
a ä
1 row found
SQL>select * from av join bv on (av.c1=bv.c2 collate swedish_1);
0 rows found
The above example also serves as an illustration to why the selected collation ordering matters. The first query results in one row, since a and ä are equal in the english language. In the final query, the swedish collation ordering is used, where a and ä are distinct letters, the equality condition therefore fails, thus the result is zero rows found.
Sometimes it is not easy to determine which collations are involved in the erroneous situation. One way to find out is to use the DESCRIBE TABLE command in BSQL.
SQL>describe table av; Table name : av Schema name : SYSADM Column name : c1 Datatype : CHARACTER VARYING(2) Collation schema : INFORMATION_SCHEMA Collation name : ENGLISH_1 SQL> SQL>describe table bv; Table name : bv Schema name : SYSADM Column name : c2 Datatype : CHARACTER VARYING(2) Collation schema : INFORMATION_SCHEMA Collation name : SWEDISH_1
The DESCRIBE TABLE command works regardless if the object in question is a view or table.