Howto convert MariaDB query to HSQLDB code

Creating tables and queries
Post Reply
pizzipie
Posts: 54
Joined: Sat Sep 13, 2008 2:28 am

Howto convert MariaDB query to HSQLDB code

Post by pizzipie »

hi,

I have the following nested query which works fine in MariaDB(MySql).

Code: Select all

 select player, timesplayed, avgscore from 
 (select player, count(score) as timesplayed, avg(score) as avgscore from bdata where year(playdate)='2017' group by pid)as t 
 order by avgscore desc;
I get the following error when designing a query in LibreOffice Base.

syntax error, unexpected $end, expecting BETWEEN or IN or SQL_TOKEN_LIKE

I would like to know how to convert this so it works in LibreOffice Base. Also where is the documentation for HSQLDB that will show me the syntax for the DB?

Thanks R
Libre Office Version: 6.0.7.3
Build ID: 1:6.0.7-0ubuntu0.18.04.10
UnklDonald418
Volunteer
Posts: 1544
Joined: Wed Jun 24, 2015 12:56 am
Location: Colorado, USA

Re: Howto convert MariaDB query to HSQLDB code

Post by UnklDonald418 »

Sometimes the Base front end gets in the way. Have you tried to run the query in the direct mode? In the query window Edit->Run SQL directly.
HyperSQL documentation can be downloaded from
http://hsqldb.org/web/hsqlDocsFrame.html
If your problem has been solved, please edit this topic's initial post and add "[Solved]" to the beginning of the subject line
Apache OpenOffice 4.1.14 & LibreOffice 7.6.2.1 (x86_64) - Windows 10 Professional- Windows 11
eremmel
Posts: 1080
Joined: Tue Dec 30, 2008 1:15 am

Re: Howto convert MariaDB query to HSQLDB code

Post by eremmel »

pizzipie wrote:

Code: Select all

 select player, timesplayed, avgscore from 
 (select player, count(score) as timesplayed, avg(score) as avgscore from bdata where year(playdate)='2017' group by pid)as t 
 order by avgscore desc;
I would consider this query wrong. Your query the field 'player' without aggregation and you group by on 'pid'. I expect that even MySql does not like this. You might also need to quote the table and field names in HSQLDB else they are considered in to be defined in uppercase. Not sure if the return of function YEAR() is a string or an number. In latter case remove single quote.
. You can write your query simple as:

Code: Select all

select "player", count("score") as "timesplayed", avg("score") as "avgscore" from "bdata" 
where year("playdate")='2017' 
group by "player"
order by "avgscore" desc
The tip of UnklDonald418, to run in direct mode is good one to obtain a decent error message. When possible revert back to non-direct mode when all works.
It's Microsoft marketing that tells you computers are qualified for non-technicians
W11 22H2 (build 22621), LO 7.4.2.3(x64)
Post Reply