[Solved] HSQLDB query has syntax errors - no clue how to fix

[pizzipie]

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.

[Villeroy]

Run it in direct mode. HSQL can interprete it but Base gets confused by the TOP 6.

[pizzipie]

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.

[Villeroy]

The object is to use the query in writing reports. I have no way of running the query directly.

The report should work with a query in direct mode or with a view. Some reports require direct mode.

[MTP]

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

[pizzipie]

SOLVED:

Thank you much MTP,

What I needed.

R