I’m migrating away from Google Sheets, so far with a lot of success, but I miss certain functions that at not included in the latest version of Calc. e.g =QUERY.
Query is a powerful function that I am finding it impossible to replicate with other functions that come with Calc
You would need the help of Base for this. It is possible to have a spreadsheet as datasource of an odb. Then you can query this datasource and drag the queries in other spreadsheets.
.
Not so powerful as using a true database, but if someone want’s to do it this way…
Thanks 
looks like It’s been there for a while. Hope someone picks it up and adds it soon so I can leave Google Sheets.
Some time ago, I played around with HSQLDB in-memory databases. You have to have Java installed for that.
This enables you to use any valid SQL clause, including join between tables. No need to create a separate .odb file.
Basic procedure is:
Define names for the data you want to use as SQL Tables
Format the header cells with the right format. HSQLDB is using that format for the data type. Currently supported is @ (text), Boolean, and numeric.
The rest is described in the sheet AnalysisStadtKreisLand
For non-German users: The raw data are a lists of Stadt (=town), Kreis (= county), and Land (=state) from Germany
Maybe a bit overkill …
inline-database - posted.ods (221.3 KB)
Without any macro code and overkill, everybody can use Base as is in order to query spreadsheet lists as well as ordinary databases by means of SQL SELECT statements.
Thank you
You are absolutely right with that. The motivation for my approach was for two reasons: (i) it uses only a single file, not an ods and an odb. This makes it easier if you want to iterate with more than one person. If you have something stable I would always recommend to go with base (ii) the raw data are stored within the calc document. So you do not risk data loss when going with the built in hsqldb database, which some users have reported to be somewhat risky
It seems that HSQLDB is going to get phased out from Libreoffice in favour of Firebird. [ Migrate from HSQLDB - The Document Foundation Wiki ]
Most likely latest HSQLDB 2.x versions wont see day of light in Libreoffice .
Thus can we have something like your suggested inline-database but uses Firebird instead of present HSQLDB 1.8
Until now, it is still the better database to be used with Base, given that you convert your embedded HSQLDB into a stand-alone database with a more recent driver when leaving the draft status.
Firebird has too many serious issues. Even MySQL/MariaDB is plays better with Base.
But note the date of the post referring to LO 6.1
Currently both projects have not much traction: Neither incrementing to a newer version of HSQLDB (and still having the ability to read old 1.8) nor Firebase being the default.
.
In any case: For compatibility the old HSQLDB will be supported/included for a very long time (if no other problems arise).
Please, what are the ‘has too many serious issue’?
Everything that keeps Firebird in “experimental” state. With a little help from @RobertG, I can handle the type errors when comparing parameters, very strange extra rules and barriers *.
However, these tripping hazards are incomprehensible for new users.
Whenever I feel the mood and click “Yes, convert embedded HSQL to Firebird”, I get a dysfunctional database. Literally every time.
WHERE ("Value" = :param OR :param IS NULL) fails, it needs to be rewritten as WHERE (:param IS NULL OR "Value" = :param)
WHERE :dateParam = "DateColumn" needs to be rewritten as WHERE :dateParam = CAST("DateColumn" AS DATE). Of course, “DateColumn” is a date column.
I do not complain of the differences between FB and HSQL. HSQL2 is just excellent and fully compatible with Base. The transition from HSQL1 to HSQL2 is a matter of minutes if you know how to do it.
SELECT :parameter*“a field” AS […]
If they can’t keep it and remove it as well, what’s the problem in upgrading to HSQLDB to latest 2.x.x version which provides exceptional features as compared to 1.8 version … Why such a half hearted approach , Why Why ???
I can’t comment on the topic of integration in the code of LibreOffice, but if some expect more than a newer database, where everything new has to be done in direct mode, because the GUI in Base don’t supports the new stuff. So there is some work to do.
.
The other problem I see is migration of old embedded dbs. IMHO “latest” hsql+db needs a transformed db.
.
But we should not transform this thread for Calc in a continuation of “Who destroyed Base”-Thread
Some comments about: