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

Creating tables and queries

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

Postby pizzipie » Tue Dec 15, 2015 12:36 am

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.
Last edited by Hagar Delest on Fri Feb 26, 2016 10:44 pm, edited 1 time in total.
Reason: tagged [Solved].
Libre Office Version: 6.0.7.3
Build ID: 1:6.0.7-0ubuntu0.18.04.10
pizzipie
 
Posts: 50
Joined: Sat Sep 13, 2008 2:28 am

Re: HSQLDB query has syntax errors - no clue how to fix

Postby Villeroy » Tue Dec 15, 2015 2:34 am

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, no OpenOffice, LibreOffice 6.4
User avatar
Villeroy
Volunteer
 
Posts: 27744
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: HSQLDB query has syntax errors - no clue how to fix

Postby pizzipie » Tue Dec 15, 2015 5:16 pm

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.
Libre Office Version: 6.0.7.3
Build ID: 1:6.0.7-0ubuntu0.18.04.10
pizzipie
 
Posts: 50
Joined: Sat Sep 13, 2008 2:28 am

Re: HSQLDB query has syntax errors - no clue how to fix

Postby Villeroy » Tue Dec 15, 2015 6:24 pm

pizzipie wrote: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.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.4
User avatar
Villeroy
Volunteer
 
Posts: 27744
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: HSQLDB query has syntax errors - no clue how to fix

Postby MTP » Wed Dec 16, 2015 5:03 pm

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
OpenOffice 4.1.1 on Windows 10, HSQLDB 1.8 split database
User avatar
MTP
Volunteer
 
Posts: 1620
Joined: Mon Sep 10, 2012 7:31 pm
Location: Midwest USA

Re: HSQLDB query has syntax errors - no clue how to fix

Postby pizzipie » Wed Dec 16, 2015 6:34 pm

SOLVED:

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
pizzipie
 
Posts: 50
Joined: Sat Sep 13, 2008 2:28 am


Return to Tables & Queries

Who is online

Users browsing this forum: No registered users and 2 guests