Is SQL UNION query in Reports supported ?

[Coder99]

I have an SQL union statement that works fine if ‘Run SQL command directly’ is selected in the query builder ie
Select Role, Given_Name, Surname from members where Role <> ‘’
union
Select Role #2, Given_Name, Surname from members where Role #2 <> ‘’
union
Select Role #3, Given_Name, Surname from members where Role #3 <> ‘’
union
Select Role #4, Given_Name, Surname from members where Role #4 <> ‘’
Group By Role ;

This returns the various roles with all the associated members from the members table.
However if I then use that query in a report (with Analyze SQL = Yes) it fails with the following error :-

org.libreoffice.report.ReportExecutionException: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ‘;
) “__LibreOffice_report_result”’ at line 8

I’m running LibreOffice Version: 6.1.3.2 (x64) on WIndows 8.1 and MariaDB 10.3.11

I would like to run the report using the SQL union statement …

Have I got something wrong or is there an issue I need to be aware of ?

[Ratslinger]

Hello,

Using somewhat of a guess of your SQL, table and report, have tested on Debian system with MariaDB and on Mint 18.3 with MySQL. Each produced errors using different connectors - tried SDBC & JDBC.

Did briefly look through Bugs and did not see anything on this. You should re-check the list & if nothing found report here → Bugzilla.

However, I did find that each would work without a problem if the SQL were used to create a View first and base the report on that. That should work until a remedy is implemented.

Edit:

Just performed a quick test on Ubuntu 18.04 with PostgreSQL (used different data) and had similar results. At this point would say it is a problem with the Union statement in general when used in Report Builder.

[Coder99]

Hi there,
Many thanks for the follow-up.
As I want to achieve a robust multi-user system I want to avoid :-

1. Clashes/problems if multiple users happen to run the same report/query/view concurrently;
2. Static views that need to be refreshed every time a record is added/deleted/updated to relevant tables.
   I shall review Bugzilla as suggested and follow-up if the need arises.
   Thanks again…

[Coder99]

Hi there,
Solved by workaround. The Query has been turned into a View, and the Report uses the View as it’s source.
This seems to have the result I’m after and will serve as a workaround.
Thanks for the info/advice, much appreciated.