/scripts/ahub.form.attachments.js
0

Another "Umlaut" bug in ADS (verified on 10.1, 11.1 and 12)

May 11, 2017 at 09:53 AM

133

avatar image

Giving this two tables:

Create Table TableA( TAFIELD1 Char( 5 ), TAFIELD2 CIChar( 50 ), TAFIELD3 Money );
INSERT INTO "TableA" VALUES( 'AAAAA', 'First Line', 12.33 );
INSERT INTO "TableA" VALUES( 'CCCCC', 'Second Line', 432.33 );
INSERT INTO "TableA" VALUES( 'XXXXÜ', 'Third Line', 1 );

Create Table TableB( TBFIELD1 Char( 5 ), TBFIELD2 CIChar( 20 ), TBFIELD3 CIChar( 10 ), TBFIELD4 CIChar( 20 ) );
INSERT INTO "TableB" VALUES( 'AAAAA', 'An der Brücke 1', '12345', 'Teststadt' );
INSERT INTO "TableB" VALUES( 'BBBBB', 'Hauptgasse 64', '54321', 'Wicheln' );
INSERT INTO "TableB" VALUES( 'XXXXÜ', 'Lange Straße 210', '12345', 'Teststadt' );

the query

SELECT ta.*, tb.*
FROM TableA ta INNER JOIN TableB tb ON ta.TaField1 = tb.TbField1
WHERE ta.TaField1 = 'AAAAA'

retrieves one row, as expected. But if we query

SELECT ta.*, tb.*
FROM TableA ta INNER JOIN TableB tb ON ta.TaField1 = tb.TbField1
WHERE ta.TaField1 = 'XXXXÜ'

we get zero rows!

We can avoid this bug using a nested query

SELECT x.* FROM (
SELECT ta.*, tb.*
FROM TableA ta INNER JOIN TableB tb ON ta.TaField1 = tb.TbField1
) x WHERE x.TaField1 = 'XXXXÜ'

which results in one row.

10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

3 Answers

Best Answer
Joachim Dürr May 12, 2017 at 10:05 AM
0

LIKE respects the collation where = uses a byte-by-byte comparison - which might fail depending on local settings;) NCHAR is Unicode and so it knwos about the special characters.

Share
10 |10000 characters needed characters left characters exceeded
Joachim Dürr May 11, 2017 at 07:44 PM
0

It works if your use LIKE instead of =

SELECT ta.*, tb.*
FROM TableA ta INNER JOIN TableB tb ON ta.TaField1 = tb.TbField1
WHERE ta.TaField1 LIKE 'XXXXÜ'
Share
10 |10000 characters needed characters left characters exceeded
Joachim Höhne May 12, 2017 at 07:27 AM
0

Nice :)

This looks somewhat official ("never use special characters in JOIN fields"), but I didn't find it in the docs.

P.S.: if I change the type of the JOIN field to "NChar" it works with "=".

Share
10 |10000 characters needed characters left characters exceeded
Skip to Content