Base says there is a SQL syntax error errantly

agerber85 · March 30, 2022, 8:13pm

I have a rather unwieldy SQL query in Base (querying a rather unweildy MySQL 5.7 DB) that is used to generate a report.

The SQL of the query is:

SELECT workorders.ID workorderID, contracts.ID contractID, customers.NAME customerName, items.ID itemID, partnos.PARTNO partnoPartno, items.SERIALNO itemSerialno, reports.ID reportID, reports.CONFORMANCE reportConformance, CONCAT(COALESCE(REPORTSREV.FIRSTNAME, ''), ' ', COALESCE(REPORTSREV.LASTNAME, '')) reportReviewedby, results.ID resultID, calibrationfeatures.NAME resultName, calibrationfeatures.TYPE resultType, calibrationfeatures.NOMINAL resultNominal, calibrationfeatures.TOP resultTop, calibrationfeatures.BOTTOM resultBottom, results.UNCERTAINTY resultUncertainty, results.CONFORMANCE resultConformance, measurementdata.ID measurementdataID, measurementdata.ID measurementdataID, CONCAT(COALESCE(OPERATORS.FIRSTNAME, ''), ' ', COALESCE(OPERATORS.LASTNAME, '')) measurementdataOperator, measurementdata.DATE measurementdataDate, measurementdata.EQUIPMENT measurementdataEquipment, measurementdata.TEMPERATURE measurementdataTemperature, measurementdata.HUMIDITY measurementdataHumidity, CONCAT(COALESCE(RESULTSENT.FIRSTNAME, ''), ' ', COALESCE(RESULTSENT.LASTNAME, '')) measurementdataResultsentby, CONCAT(COALESCE(RESULTSREV.FIRSTNAME, ''), ' ', COALESCE(RESULTSREV.LASTNAME, '')) measurementdataResultsrevby
FROM WORKORDERS JOIN CONTRACTS ON 
	CONTRACTS.ID = WORKORDERS.CONTRACT JOIN CUSTOMERS ON
	CUSTOMERS.ID = CONTRACTS.CUSTOMER JOIN ITEMSINWORKORDERS ON
	ITEMSINWORKORDERS.WORKORDER = WORKORDERS.ID JOIN ITEMS ON
	ITEMS.ID = ITEMSINWORKORDERS.ITEM JOIN RECEIVEDITEMS ON
	RECEIVEDITEMS.ITEMINWORKORDER = ITEMSINWORKORDERS.ID JOIN PARTNOS ON
	PARTNOS.ID = ITEMS.PARTNO JOIN CALIBRATIONFEATURES ON
	CALIBRATIONFEATURES.PARTNO = PARTNOS.ID JOIN REPORTS ON
	REPORTS.ITEMINWORKORDER = ITEMSINWORKORDERS.ID JOIN RESULTS ON
	RESULTS.CALIBRATIONFEATURE = CALIBRATIONFEATURES.ID JOIN MEASUREMENTDATA ON
	MEASUREMENTDATA.ID = RESULTS.MEASUREMENTDATA JOIN PERSONNEL RESULTSREV ON
	RESULTSREV.ID = MEASUREMENTDATA.RESULTSREVBY JOIN PERSONNEL RESULTSENT ON
	RESULTSENT.ID = MEASUREMENTDATA.RESULTSENTBY JOIN PERSONNEL OPERATORS ON
	OPERATORS.ID = MEASUREMENTDATA.OPERATOR JOIN PERSONNEL REPORTSREV ON
	REPORTSREV.ID = REPORTS.REVIEWEDBY
GROUP BY RESULTS.ID
ORDER BY WORKORDERS.ID, ITEMSINWORKORDERS.ID

Whenever I go to launch the report, I get:

The document “Overview” could not be opened.

Syntax error in SQL statement

But the Query runs fine. Help!? Is this a bug?

Oh, and this is my LO version info:

Version: 7.0.6.2 (x64)
Build ID: 144abb84a525d8e30c9dbbefa69cbbf2d8d4ae3b
CPU threads: 4; OS: Windows 10.0 Build 19044; UI render: Skia/Raster; VCL: win
Locale: en-US (en_US); UI: en-US
Calc: threaded

Ratslinger · March 30, 2022, 8:29pm

Hello,
Edit the report and insure on the Data tab of the report that Analyze SQL command is set to No.

RobertG · March 31, 2022, 8:04am

If you set Analyze SQL command → No sorting and grouping in the report won’t work. I prefer to use views for a report. So create a view of your query and set this as data source to the report.

keme1 · March 31, 2022, 12:26pm

Shooting into the darkness here…
Could it be that Base parses the query before submitting it, and this parsing expects AS for the aliased joins to PERSONNEL. The AS keyword is optional, which might explain why the query works anyway. (You say “query runs fine”, so I take it that you get the expected query result.)

Edit: Upon second reading I realize that this may also be the essence of Villeroy’s answer. Weird that this breaks the flow as described.

agerber85 · March 31, 2022, 12:31pm

That’s correct. I’ll try adding the AS keyword and seeing if that changes anything.

agerber85 · March 31, 2022, 12:48pm

@RobertG,
Just figured out what you meant by “create a view”, and when I did what you said, everything worked as desired. Thanks!

Villeroy · March 30, 2022, 8:29pm

Right-click>Edit… the report.
menu:View>Report Navigator
Get the properties of the report, tab “Data”.
Property “Analyse SQL command” = NO (pass through to the MySQL driver)

agerber85 · March 31, 2022, 12:13pm

Thanks, everyone, for your input. I set Analyze SQL command to No, and now when I execute the report, I get:

An error occurred while creating the report.

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘"__LibreOffice_report_result"’ at line 18

This is odd, because I don’t have anything like that in my query SQL (I don’t even know what that quoted language means.), and line 18 of my query is

GROUP BY RESULTS.ID

Is it possible that Base truncates my query SQL into fewer lines and wraps it in other SQL in order to use it for a report, and that there was a glitch in the wrapping this time? What is the “Analyze SQL command” supposed to do?
@RobertG, please explain what you mean by “create a view of your query”. I already have a query set up in the “Queries” section of Base with that SQL, and it runs just fine. It is also already the data source of report that is complaining of the syntax error.

RobertG · March 31, 2022, 12:50pm

Your Query has code GROUP BY. Could be you wan’t to group the report also and have set groups, not only the section Detail. So the Report Builder tries to group a query again and GROUP BY … GROUP BY won’t work.

You have created a query in the “Queries” section. Now a right mouse click on the query and Create as view. The query will appear as a view in “Tables” section. It will be executed in MySQL directly. Report Builder should connect to this view and will handle this view without any problems.

agerber85 · April 1, 2022, 3:49pm

Thanks! That would explain why creating a view of the query and settting that view as the data source worked. I assume that if I have multiple fields selected for grouping in my report (nested groups), The GROUP BY of the report is a "GROUP BY [field1], [field2], [field3]…