Query in .odb not correctly interpreting CONCAT_WS()

[agerber85]

My database engine is MySQL. I have a query I’m building in Base in SQL mode, and it’s not correctly interpreting the SQL.

SELECT CONCAT_WS(" ", FIRSTNAME, LASTNAME) FROM PERSONNEL WHERE ID = 1

If I execute that outside Base, it gives me the desired results. Inside base, I get:

Unknown column ’ ’ in ‘field list’

I’ve tried replacing the space with a different string for a separator, and it keeps coming back saying that string is an unknown column. I have other places in this .odb that use concat_ws, and AFAIK, they’re still working correctly.

Is this a new bug? Does anyone know how to work around it?

[elmau]

always indicate the DB engine you use

[agerber85]

Got it! I had to enclose the space in single quotes rather than double quotes. Otherwise, when I re-opened the query, LO had kindly replaced them with those ubiquitous nasty backticks, meaning it was going to interpret it as a field, table, or alias. Once I did that, it worked as expected

[Nise]

I use version 7.2.62. And I can’t use this function even putting single quotes.

[Ratslinger]

@Nise
Have just tested with MySQL v5.7.26 (ODBC) and v8.0.30 (JDBC) and both work. Both linux systems.
Quotes or apostrophes are dependent upon pass through being on or off.
You did not post your MySQL version, SQL used, the error you are getting, the OS or the connection type being used.
This item has been available since MySQL v 4.x
Of course if not using MySQL or other database with this function, that could be the problem.

[Nise]

I achieved:
SELECT campo1||’ - '||campo2 AS NOME FROM TABELA

I use version 7.2.62 Libreoffice

[Wanderer]

@Nise

I use version 7.2.62

I your case: Have you seen the question you were extending relates to MySQL (MariaDB)? If you did not connect to MySQL you will probably use HSQLDB.
Therefore it is better not to extend older questions.
.
Note also, your solution will fail, if campo1 or campo2 is NULL.