LIBRE BASE Query won't run

[JohnJPDX]

I have 20 lines of data in a table. I created a query sorting data by date and limited it to only the 10 newest lines. That query works fine. When I try to create a query taking one column from the first query and averaging it the query fails.

Error message starts with: Cannot be in ORDER BY clause in statement [SELECT AVG( “Differential” ) AS “Differential” FROM ( SELECT “Differential” AS “Differential”, “Player” AS “Player”, “Date”

Don’t understand why it can’t run a query off another query with a SORT in it. I can’t even just create a duplicate query of the first query.

Just updated to 6.3.4.2 running HSQLDB.

[Ratslinger]

Hello,

Difficult to tell your problem without the complete SQL from each.

This seems to be a case of the HSQLDB version. Appears you are using a sub query which works with newer versions but not with the one with the one included with Base. It is very old - v1.8

For this version it may be best to create a View for the sub query and use that as the source. In the View, include the sorting but use the Limit aspect in the Query. This seems to work for this version.

[Ratslinger]

As with all questions, if this answers your question please tick the (upper left area of answer). It helps others to know there was an accepted answer.

[JohnJPDX]

Ok, how can I get the newer version of the DB?

[Ratslinger]

@JohnJPDX,

The method to use a newer version of HSQLDB is to use a split database. The process can be found here →
[Wizard] Create a new ‘split’ HSQL 2.x database. This also allows you to change to a different version easily.

However, LO has also included the Firebird embedded database as its’ replacement of HSQLDB v1.8 embedded (no time frame set). Although there are still modifications being done, most problems have workarounds (many posted on this site). Have had this running and answering questions here for over 18 months now (search Firebird). There are also many other databases you can use such as MySQL, MariaDB, PosgreSQL and others.

The choice is yours and you should always investigate the capabilities of a database (all have manuals available with an internet search) before settling on one.

[JohnJPDX]

Thanks for all the help. I ended up going back thru all the parent queries and just sorting them by date from the tool bar rather than a SORT statement. Somehow this worked and the data all came down in date order and I could use the limit to pull the desired data. I don’t know how this will work when I add new data. Hopefully the date sort will remain in place.

[BrianStew]

I am also having this problem.

Version: 6.4.6.2
Build ID: 1:6.4.6-0ubuntu0.20.04.1
CPU threads: 4; OS: Linux 5.4; UI render: default; VCL: gtk3;
Locale: en-GB (en_GB.UTF-8); UI-Language: en-GB
Calc: threaded

I have an HSQLDB embedded database which contains a query with a sort.
I have a second query which uses the 1st query as a source.
This was working fine until last week - I had been using it regularly with no problems.

I have managed to duplicate this problem, reducing the queries to the absolute minimum to replicate the problem.
(My actual queries are much more complicated but I have reproduced the problem with the following)

Query 1 = SELECT “Name” FROM “Customers” ORDER BY “Name” ASC

Query 2 = SELECT “Name” FROM “Query1”

Query 1 works fine (as you would expect).
Query 2 fails:-

The data content could not be loaded.

Cannot be in ORDER BY clause in statement[SELECT"Name" FROM (SELECT “Name” FROM “Customers” ORDER BY “Name” ASC) AS “Query1”]

Removing the "ORDER BY “Name” ASC from Query 1 removes the error

I should point out again that this worked perfectly until last week (I don’t know what changed, some update probably). I also don’t know what versions I was using before the change but I was using Ubuntu 20.04.

I also noticed that it is only possible to create a new database as HSQLDB embedded. There used to be the option of selecting either HQSLDB or Firebird but the Firebird option is no longer a choice.

I have also noticed that BASE seems to crash a lot, an awful lot and has to be recovered, but I guess that is a separate issue.

[Ratslinger]

Hello,

This is not ans answer. It should either be a comment or a new question.

Just change Query 1 to a View.

BTW - I have no unusual crashing problem with Base. For Firebird, you once again need experimental features turned on for this to be available. If off, you can still use existing Firebird Base files but just can’t create.

[BrianStew]

Ratslinger,
You are right, it is not an answer, sorry about that, I’m not used to forums.
I’ve just tried to change it to a comment but it wont let me, something about being over 1000 characters.

I’ve enabled experimental features and now firebird is available but I also get prompts to update my HSQLDB database to firebird and it’s too complicated for me to convert. Most of my queries fail, as do my forms and a macro.

As I previously mentioned my query with subquery did work fine until about a week ago. It’s really annoying when updates break things.

[Ratslinger]

Just to note again, you can get it working again by changing Query 1 to a View.