Unable to use TRIM() function in Libreoffice Base connected to mariadb

[Pitneyb]

i’m trying to create a query in base using the following select statement to delete 'The ’ from the beginning of film titles to provide a sort order.

SELECT title FROM tblprogram ORDER BY TRIM(LEADING 'The ’ FROM title);

Every time I get an SQL config error , but the command will run directly on mariadb.

[ma-3.2.2][11.5.2-MariaDB]You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'LEADING ‘The ’ FROM title )’ at line 1 at /usr/src/debug/libreoffice-fresh/libreoffice-24.8.0.3/connectivity/source/drivers/odbc/OTools.cxx:357

I hope someone can help me out. I have tried various combinations of quotes in case it was that.
Although i am new to base and mariadb, I have used msAccess and Microsoft SQL in the past, but it was over 20 years ago so I am probably a bit rusty.

[Villeroy]

“Queries” in Base are SELECT statements. If you create them with Base tools (query designer or wizard), they are parsed queries. While in SQL view, you can turn on “direct SQL mode”. MS Access used to call this “pass-through query”. In this mode, the statement is directly passed to the database engine, so the syntax needs to be compatible with the respective engine.
Views are SELECT statements stored in the realm of the database engine. They are translated to the engine’s SQL dialect (“direct SQL”).
SQL statements not starting with SELECT (UPDATE, DELETE, INSERT, CREATE, ALTER, DROP) can be executed in the SQL window (Tools>SQL…). These statements are always run in “direct SQL mode”. In my memory MS Access used to name these non-SELECT statements “action queries”.
A record set returned by direct SQL is always read-only.
Quite often, direct SQL yields more precise error messages which is why I use it for debugging.

For form/subform relations and parameter queries, you need queries in “parsed mode” (parsed by Base, not direct SQL). Possible workaround: SELECT * FROM "direct_SQL_Query_or View"interpretes the record set in Base.

Quoting your table/column names with backticks, I would try to run the query in direct SQL mode.
I don’t have any MySQL at hand right now. Just try to point out, how/when SQL statements are interpreted by Base or the respective engine.

[RobertG]

Runs here with MariaDB without any problem, when Edit → Run SQL command directly is set in query designer.

[nicholas59]

This post was flagged by the community and is temporarily hidden.

[Pitneyb]

Thanks to everyone for your help.
I didn’t know that was an option and it now works as intended.