Differences between view SQL design, run SQL directly, table data view

[frankpapa]

I’m trying to debug a SELECT statement containing a UNION. I am running Libreoffice Base 7.3.7.2 on Linux Mint 21.3, and I’m using native embedded HSQLDB. I’m getting different results in different places, and I don’t understand what or why there are differences.

• When I run the view in the view SQL editor with “Run SQL directly” enabled, it gives me the expected result.
• When I run the view in the view SQL editor without “Run SQL directly” enabled, I get a SQL syntax error (with no specific error message pointing to the syntax problem).
• When I double click on the view from the main screen to open the Table Data View, I get some of the results I expect. It looks like I’m getting data from the first join in the UNION, but not the other. When I reverse the order of the UNION, I again get data from the first join in the UNION, but not the other.

I have run this query through a few different LLM’s and none of them flagged any serious issues. How can I go about debugging this?

[Wanderer]

@frankpapa

run this query through a few different LLM’s and none of them flagged any serious issues. How can I go about debugging this?

• Wait until sombody trains a LLM more about LibreOffice.
• You already found LibreOffice does not understand every SQL statement. So use SQL direct mode and LibreOffice will give you the result from the database. and this should be identical to Tools>SQL
• Sometimes it helps for further processing to create a view from the query (option in context menu of query)
• As I assume you are a NI you will have your reasons for not posting your SELECT/UNION statement here. So I can only recommend prayer or wish you good luck with debugging.

[RobertG]

UNION won’t work in GUI. You have to execute in SQL directly.
If there are rows with the same content in a query with UNION connection only one of this values will be shown.
You couldn’t order a query, which doesn’t work in GUI (only direct SQL) in GUI. Ordering such a query is only possible in SQL directly or if you create a view of the query.

[frankpapa]

That seems to correlate with what I’m seeing. I didn’t know about the limitations in Base in terms of queries involving UNION. Thanks for that!

I’m connecting to this data via a linked range in Calc, and I’m seeing the same incomplete results of this view in Calc that I see in my third scenario. Is there any way I can tell Base to exclusively use direct SQL when running these particular views?

My SQL:

(
select “MonthName” as “Month”,
“YearName” as “Year”,
SUM(“Gain”) as “BLAH”,
NULL as “SCHD”
FROM
(
SELECT
A.DSYMBOL as “Symbol”,
CASE A.DACTION WHEN ‘Full Redemption Adj’ THEN A.DAMOUNT+(select sum(B.DAMOUNT) from “TDATA” B where B.DSYMBOL = A.DSYMBOL and B.DACTION=‘Buy’) ELSE NULL END as “Gain”,
monthname(A.DDATE) as “MonthName”,
year(A.DDATE) as “YearName”
FROM “TDATA” A
WHERE A.DDESCRIPTION LIKE ‘BLAH%’
and “Gain”>0
) AS SUBSEL1
GROUP by
“MonthName”,
“YearName”
)

UNION ALL

(
select “MonthName” as “Month”,
“YearName” as “Year”,
NULL as “BLAH”,
SUM(“Totes”) as “BLEH”
FROM
(
SELECT A.DDATE as “Date”, A.DAMOUNT as “Totes”,
monthname(A.DDATE) as “MonthName”,
year(A.DDATE) as “YearName”
FROM “TDATA” A
WHERE A.DSYMBOL = ‘BLEH’
AND A.DACTION = ‘Reinvest Dividend’
) AS SUBSEL2
GROUP by
“MonthName”,
“YearName”
)

order by “Month”, “Year”

[Wanderer]

@frankpapa

Is there any way I can tell Base to exclusively use direct SQL when running these particular views?

Somebody found out:

@frankpapa

When I run the view in the view SQL editor with “Run SQL directly” enabled, it gives me the expected result.

So, why not save the query, wich gives the desired result. Calc will get the same data then. The mode is set in the query Editor, because it is a setting for the query, not something global…

[frankpapa]

Yup, I just figured that out, as well! Yes, I can save it as a query (instead of a view), and then I can link to that query from Calc.

It seems that Base makes some changes to views when I save them, but it doesn’t do the same to queries.

Thanks to both of you for help!

[Wanderer]

@frankpapa

It seems that Base makes some changes to views when I save them, but it doesn’t do the same to queries.

No, it tells the database to handle this. Therefore the view is shown as an additional table. A query is transmitted to the database when you run it, and can be augmented by parameters like for example
WHERE Name=:getName