Hi,
Need some help writing an SQL query in LibreOffice Base. I'm used to MySql syntax.
I've tried to use LIMIT but that doesn't work either. Is there a forum like MySql Forums for
HSQLDB? Help greatly appreciated.
R
My query is:
SELECT TOP 6 "id", "playdate", "player", "score" FROM "bridge"."bdata" AS "bdata" ORDER BY "playdate", "Score" DESC
Error is:
SQL Status: HY000
Error code: 1000
Syntax error in SQL statement
NO clue as to what is wrong.
[Solved] HSQLDB query has syntax errors - no clue how to fix
[Solved] HSQLDB query has syntax errors - no clue how to fix
Last edited by Hagar Delest on Fri Feb 26, 2016 10:44 pm, edited 1 time in total.
Reason: tagged [Solved].
Reason: tagged [Solved].
Libre Office Version: 6.0.7.3
Build ID: 1:6.0.7-0ubuntu0.18.04.10
Build ID: 1:6.0.7-0ubuntu0.18.04.10
Re: HSQLDB query has syntax errors - no clue how to fix
Run it in direct mode. HSQL can interprete it but Base gets confused by the TOP 6.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Re: HSQLDB query has syntax errors - no clue how to fix
Thanks for the reply Villeroy,
The object is to use the query in writing reports. I have no way of running the query directly.
I did stumble around and come up with this:
SELECT "id", "playdate", "player", "score" FROM "bridge"."bdata" AS "bdata" ORDER BY "playdate" DESC LIMIT 6
It works but, as you say, Base gets confused easily. LIMIT 0, 6 will not work and I can't find any reference documentation for such statements in HSQL. The documentation I have found talks about 'LIMIT' to limit the records shown in a million record database blah blah blah.
The object is to use the query in writing reports. I have no way of running the query directly.
I did stumble around and come up with this:
SELECT "id", "playdate", "player", "score" FROM "bridge"."bdata" AS "bdata" ORDER BY "playdate" DESC LIMIT 6
It works but, as you say, Base gets confused easily. LIMIT 0, 6 will not work and I can't find any reference documentation for such statements in HSQL. The documentation I have found talks about 'LIMIT' to limit the records shown in a million record database blah blah blah.
Libre Office Version: 6.0.7.3
Build ID: 1:6.0.7-0ubuntu0.18.04.10
Build ID: 1:6.0.7-0ubuntu0.18.04.10
Re: HSQLDB query has syntax errors - no clue how to fix
The report should work with a query in direct mode or with a view. Some reports require direct mode.pizzipie wrote:The object is to use the query in writing reports. I have no way of running the query directly.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Re: HSQLDB query has syntax errors - no clue how to fix
In your query window, you can choose Edit→Run SQL Command Directly to tell Base to just send the command straight to the HSQLDB engine and not try to parse it first.
The HSQLDB version that comes embedded inside Base is an old version, 1.8. The syntax documentation is at http://www.hsqldb.org/doc/1.8/guide/ch09.html. If you upgrade to a current version ([Wizard] Create a new 'split' HSQL 2.x database) then the documentation is at http://hsqldb.org/web/hsqlDocsFrame.html
The HSQLDB version that comes embedded inside Base is an old version, 1.8. The syntax documentation is at http://www.hsqldb.org/doc/1.8/guide/ch09.html. If you upgrade to a current version ([Wizard] Create a new 'split' HSQL 2.x database) then the documentation is at http://hsqldb.org/web/hsqlDocsFrame.html
OpenOffice 4.1.1 on Windows 10, HSQLDB 1.8 split database
Re: HSQLDB query has syntax errors - no clue how to fix
SOLVED:
Thank you much MTP,
What I needed.
R
Thank you much MTP,
What I needed.
R
Libre Office Version: 6.0.7.3
Build ID: 1:6.0.7-0ubuntu0.18.04.10
Build ID: 1:6.0.7-0ubuntu0.18.04.10