Ask Your Question
0

Is SQL UNION query in Reports supported ? [closed]

asked 2019-06-10 07:01:05 +0200

Coder99 gravatar image

updated 2019-06-17 08:17:15 +0200

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 ?

edit retag flag offensive reopen merge delete

Closed for the following reason the question is answered, right answer was accepted by Coder99
close date 2019-06-17 08:17:26.075694

2 Answers

Sort by » oldest newest most voted
0

answered 2019-06-11 07:51:30 +0200

Coder99 gravatar image

updated 2019-06-11 07:53:24 +0200

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.

edit flag offensive delete link more
0

answered 2019-06-10 17:31:49 +0200

Ratslinger gravatar image

updated 2019-06-10 17:41:14 +0200

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.

edit flag offensive delete link more

Comments

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 gravatar imageCoder99 ( 2019-06-11 06:52:43 +0200 )edit

Question Tools

1 follower

Stats

Asked: 2019-06-10 07:01:05 +0200

Seen: 70 times

Last updated: Jun 17