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?