[Solved] Listbox multiple column query returns zeros

[Klavs]

I have followed the instruction viewtopic.php?p=164740#p164740. It worked fine when I had my database as embedded Base file. However, I ported it to an MariaDB SQL server, now using LibreOffice as a frontend. Since then the sintax suggested returns 0 values when the sql is run. What could be the problem? In addition I cannot use double quotes as they are automatically changed to single quotes when I save the statement.

[Klavs]

By trial and error and some search I discovered that the syntax with || can be replaced with the following syntax:

Code: Select all

SELECT CONCAT( [Column1], ' ', [Column2] ), `Column3` FROM

etc.

[UnklDonald418]

Any time you change back-ends it is not unusual to encounter some SQL syntax differences.
While the ANSI standards define the core SQL syntax, each vendor differentiates itself by adding extensions to the standard.
As you discovered not all SQL implementations currently allow double pipes to replace the CONCAT function. It's too bad because 'a' || ' ' || 'b' || ' ' || 'c' is much easier to type and read than the standard CONCAT(CONCAT(CONCAT(CONCAT('a', ' '),'b'), ' '),'c'). MariaDB seems to have extended the CONCAT function because the syntax you used for your solution will fail under HSQL.
In a effort to standardize case sensitivity issues of the underlying operating system HSQL requires any table and table column names containing lower case characters be enclosed in double quotes. It appears MariaDB takes a different approach
https://mariadb.com/kb/en/mariadb/ident ... nsitivity/