Base - Query not displaying in Report

[the_watchmann]

Hi
I have a database with sqlite3 backend. Pretty old db in use since 2008.
Anyway, I have a query where I list monthly payments and one of the tables is a sql query getting the last payment date for each payee.
Previously the result would be displayed on my report but of late the report displays a blank line where the date should be. The query itself works fine and displays the given date, however it no longer displays on my report.
It’s more than possible that something somewhere changed between LO6 & LO7 and I am not aware of it.
My question is, should a bug be filed or am I doing something wrong?
I’m attaching screenshots of the query and resultant report. last column of the query displays the date I need affixed to my report under ‘Last paid:’

This is the query :
SELECT “SUPPLIER”.“SUPPLIERNAME”, “SUPPLIER”.“ID”, “SUPPLIER”.“BANK”, “SUPPLIER”.“ACB”, “SUPPLIER”.“ACCOUNT”, “PAYMENTS”.“SETTLEMENT”, “SUPPLIER”.“TERMS”, “PAYMENTS”.“DATE_DUE”, “PAYMENTS”.“INVDATE”, “PAYMENTS”.“AMOUNT”, “PAYMENTS”.“ORDERNO”, “PAYMENTS”.“DELIVERY_DATE”, “PAYMENTS”.“DESCRIPTION”, “PAYMENTS”.“INVOICE”, “PAYMENTS”.“OUTSTANDING”, ( SELECT MAX( “DATE_PAID” ) AS “LAST_PAID” FROM “PAYMENTS” WHERE “SUPPLIER”.“SUPPLIERNAME” = “PAYMENTS”.“SUPPLIER” ) FROM “PAYMENTS”, “SUPPLIER” WHERE “PAYMENTS”.“DATE_DUE” <= {d ‘2022-11-08’ } AND “PAYMENTS”.“OUTSTANDING” <> ‘0’ AND “PAYMENTS”.“SUPPLIER” = “SUPPLIER”.“SUPPLIERNAME” AND “PAYMENTS”.“DATE_DUE” >= {d ‘2021-09-16’ } ORDER BY “SUPPLIER”.“SUPPLIERNAME”, “PAYMENTS”.“INVDATE” ASC, “PAYMENTS”.“INVOICE” ASC

Thanks for any feedback

Linux Mint 20.3 Cinnamon on HP Elitebook 8570p,
LO 7.4.2.3

[RobertG]

I would prefer to create a view from the query. Views will run better with Report Builder.

@the_watchmann

Previously the result would be displayed on my report but of late the report displays a blank line where the date should be.

Do really mean “date” or should it be “data”?

[the_watchmann]

Hi RobertG
Thanks for the reply. Date or data. Should be data, I guess, but in this instance the data is a date or dates. I tried the view, still does not work though, unfortunately. It seems that having a query inside a query may be a problem for the report builder, not sure.

[RobertG]

@the_watchmann

I tried the view, still does not work though, unfortunately. It seems that having a query inside a query may be a problem for the report builder, not sure.

If you have created a view there is no query inside a query for the Report Builder. Report Builder handles a view like a table. You have changed the datasource for the Report-Builder by opening the report for editing and switch Data of the report to get the view?

Which version of LO do you use? If you haven’t installed original LO directly from LO-Website please try by installing such a version parallel to the version you are using from your distribution.

[the_watchmann]

ah, it was the query eventually. Thanks cpb and RobertG. cpb, your query did it, and Robert, yes, when I changed the report to the view, somehow it didn’t change the items individually. Quick change and voila, it all works now! Much appreciated!

[flywire]

@RobertG

I would prefer to create a view from the query.

An interesting comment. I understand SQL considers Select From [Where] [etc] returns a view as a virtual dataset regardless of whether the selection is from a table or query. Base certainly has some idiosyncrasies.

[cpb]

you used the GUI to create your query which is fine but I need to show the sql in its pure unaltered form.
I have given the offending statement an alias.

1. make a copy of the original query, for backup.
2. copy the code below.
3. right click the original query and select ‘Edit in SQL View’.
4. delete all and paste the code.
5. execute.
6. if all is good then save using original query name, ELSE ABORT and post the error message.
7. edit the report and update the appropriate text box to show the field “LAST_PAID”.

SELECT "SUPPLIER"."SUPPLIERNAME", 
"SUPPLIER"."ID", 
"SUPPLIER"."BANK", 
"SUPPLIER"."ACB", 
"SUPPLIER"."ACCOUNT", 
"PAYMENTS"."SETTLEMENT", 
"SUPPLIER"."TERMS", 
"PAYMENTS"."DATE_DUE", 
"PAYMENTS"."INVDATE", 
"PAYMENTS"."AMOUNT", 
"PAYMENTS"."ORDERNO", 
"PAYMENTS"."DELIVERY_DATE", 
"PAYMENTS"."DESCRIPTION", 
"PAYMENTS"."INVOICE", 
"PAYMENTS"."OUTSTANDING", 
( SELECT MAX( "DATE_PAID" ) FROM "PAYMENTS" WHERE "SUPPLIER"."SUPPLIERNAME" = "PAYMENTS"."SUPPLIER" ) AS "LAST_PAID"
FROM "PAYMENTS", "SUPPLIER" 
WHERE "PAYMENTS"."DATE_DUE" between '2021-09-16' and '2022-11-08' 
AND "PAYMENTS"."OUTSTANDING" <> 0 
AND "PAYMENTS"."SUPPLIER" = "SUPPLIER"."SUPPLIERNAME"
ORDER BY "SUPPLIER"."SUPPLIERNAME", "PAYMENTS"."INVDATE" ASC, "PAYMENTS"."INVOICE" ASC

[the_watchmann]

Hi cbp
Actually, the original query was done via sqlitestudio and it seems libreoffice ‘translated’ the query somehow. Irrespective, the query works fine and the results are as expected, both your query and LO’s translation of my original query via sqlite3. I tested your code and the report builder still doesn’t read the ‘last paid’ data. It used to work with LO6. The hiccup came in with LO7. Something changed, I think, and it seems (to me anyway) that report builder no longer reads a query inside a query. Thanks for the reply