How to use sqlite CTE in Base

[ngdias]

I have a working sqlite SQL statement using Common Table Expressions that I’m trying to use inside Base.

The SQL structure looks like this

WITH 
    Query1 AS (
      SELECT COUNT(*) AS total
      FROM Table
      WHERE field = :param
    ),
    Query2 AS (
      SELECT COUNT(*) AS total_qual
      FROM Table
      WHERE field = :param
    )
    SELECT *
    FROM Query1, Query2

This doesn’t work, even adding double quotes. I submitted this to ChatGPT and it came back with the explanation that I should use subqueries instead:

SELECT cq.*, tq.*, 
FROM 
    (SELECT COUNT(*) AS total
     FROM Table
     WHERE field = :param
    (SELECT COUNT(*) AS total_qual
     FROM Table
     WHERE field = :param
INNER JOIN [...]

This also doesn’t work, BUT it works with just 1 subquery instead of the 2 I need.
I submitted this to ChatGPT again and it came back with another solution that uses ‘nested subqueries or join the subqueries in a way that HSQLDB supports’:

SELECT cq.*, tq.*, 
FROM 
   Table
INNER JOIN 
    (SELECT COUNT(*) AS "total_qual"
     FROM "Table"
     WHERE "field" = :param
ON [...]
CROSS JOIN 
    (SELECT COUNT(*) AS "total"
     FROM "Table"
     WHERE "field" = :param

But this also doesn’t work.

My preferred solution by far would be to just run native sqlite SQL directly with parameter substitution, is this possible?

Otherwise, what rules do I need to follow to convert CTEs to SQL that Base supports?

[cpb]

I have never used sqlite but it looks as though you are overcomplicating things.
maybe something like this very basic generic sql would be sufficient.
.
replace “my_table_name” with your actual table name.
replace “my_field_name” with your actual field name.

select
	my_field_name,
	count(*) c
from
	my_table_name
where
	my_field_name in(:field_content_1, :field_content_2)
group by
	my_field_name
;

[Wanderer]

@ngdias

to just run native sqlite SQL directly with parameter substitution, is this possible?

No. Direct mode means, Base will not touch your query. Parameter substitution with :param requires Base to modify your query.
.
The feature could be implemented, but don’t expect changes soon for Base. (And you should file an request for enhancement at bugzilla, before expecting amy changes…)
.
Work-around: You could use filter-tables, but that clutters your clean tables approach, wich you told in another thread, if I’m remembering right.
.
As your CTE will require direct mode, you can not combine it with using :param, as your code did. (I did not do any further inspection of your code.)

[ngdias]

Thank you for the reply.
.
I see so many roadblocks and workarounds to get Base to do simple stuff with reporting that I started thinking it won’t work for me. As I was mulling over this, I had the idea to look for other free tools to report database data, as I really don’t need to use Base to edit the database in any way. I just need a free and convenient way to display the data without having to write a bunch of code around the SQL. I found several, mostly marketed around BI (business intelligence) and after a cursory review, I decided to give Grafana a go.
.
Grafana is not exactly the ideal fit for my purpose, as it was designed with other used cases in mind and expects a lot of time-based data. But it can be adapted easily to display any data and the interface is a breeze to use. Plus it comes with a bunch of graph templates ready to go, which would also require some complicated maneuvering with Calc to embed on Base. I got more work done in a day with Grafana than with Base in a week with all the confusing workarounds and tricks to get simple stuff working. And it accepts any working SQL and the parameter substitution is simple and doesn’t require big changes in the SQL.
.
https://kexi-project.org/ was also an option, but the interface looks a bit dated, doesn’t seem to have as many features as Grafana, apparently it’s not possible to open external databases (Kexi Project - "Microsoft Access for Linux" - Development) and it requires the installation of a 3rd party software first.
.
I hope this helps other people that also find stumbling blocks with Base. Base seems to be extremely outdated compared to Access, which it was supposed to replace, right? Here is another link promoting Budibase and offering analysis on other alternatives 10 Microsoft Access Alternatives for 2024

[RobertG]

@ngdias : You report so much about other apps, which could work with databases. This list here is for LibreOffice, and database management in LibreOffice will be done with Base.
Ask for help with Base, read the Base Guide (English documentation | LibreOffice Documentation - LibreOffice User Guides), use the possibilities of Base.
Have created many databases in Base. Never used parameters, because I use forms for filtering the content of a datasource and use a filter table for it.

[Wanderer]

@ngdias

compared to Access, which it was supposed to replace, right?

Wrong. StarOffice was not designed to be a replacement for MS-Office. Several parts use different approaches. Base is a tool to connect Star/Open/LibreOffice to different databases from csv over dBase to embedded HSQLDB and Multi-User systems like MySQL, postgres etc.

The other main difference is the use of Base in Writer or Calc as datasource, while MS decided to sell Access separate. So Word and Excel needed some additions where LibreOffice will use Base instead.

As you ruled out filter tables as well as macros and now wishes to avoid writing SQL your decision is right for you. Good luck.