[Solved] Report from Query with UNION and parameters?

Getting your data onto paper - or the web - Discussing the reports features of Base
Post Reply
One Big Yawn
Posts: 24
Joined: Sat Jul 14, 2012 6:09 am

[Solved] Report from Query with UNION and parameters?

Post by One Big Yawn »

I'm running a query as such:

Code: Select all

SELECT BenID ID, CONCAT_WS(',', BenSName, BenFName) NAME, DOB, Gender Sex, Island,
FortnightRate Amount, BankNo, BankSuff
FROM Beneficiary b, Apply a, BenefitType bt
WHERE b.BankNo IS NOT NULL
AND b.benid = a.beneficiary
AND a.benefit1 = bt.bentype
AND bentype LIKE '%Child%'
AND Ben1Active = 'Active'
AND DirSignDate <=
	(SELECT DATE_ADD(CURDATE(), INTERVAL (11 - IF(DAYOFWEEK(CURDATE())=1, 10, 
		DAYOFWEEK(CURDATE()))) DAY) AS NextWednesday)
AND Ben1EffDate <=
	(SELECT DATE_ADD(CURDATE(), INTERVAL (11 - IF(DAYOFWEEK(CURDATE())=1, 10, 
		DAYOFWEEK(CURDATE()))) DAY) AS NextWednesday)
UNION
SELECT BenID ID, CONCAT_WS(',', BenSName, BenFName) NAME, DOB, Gender Sex, Island,
FortnightRate Amount, BankNo, BankSuff
FROM Beneficiary b, Apply a, BenefitType bt
WHERE b.BankNo IS NOT NULL
AND b.benid = a.beneficiary
AND a.benefit2 = bt.bentype
AND bentype LIKE '%Child%'
AND Ben2Active = 'Active'
AND DirSignDate <=
	(SELECT DATE_ADD(CURDATE(), INTERVAL (11 - IF(DAYOFWEEK(CURDATE())=1, 10, 
		DAYOFWEEK(CURDATE()))) DAY) AS NextWednesday)
AND Ben2EffDate <=
	(SELECT DATE_ADD(CURDATE(), INTERVAL (11 - IF(DAYOFWEEK(CURDATE())=1, 10, 
		DAYOFWEEK(CURDATE()))) DAY) AS NextWednesday);
It works fine if I run the SQL directly. However, when I generate a report from the query and try to open it, I get
Syntax error in SQL Expression
I believe Base has a problem with UNION, but is there a workaround for this?

(Also, if anyone knows what GROUP BY WITH ROLLUP syntax I would use to get totals for Amount for each island, that'd be great)
Last edited by One Big Yawn on Tue Jul 31, 2012 2:27 am, edited 5 times in total.
LibreOffice 3.5, MySQL 5.5, Windows 7
User avatar
Sliderule
Volunteer
Posts: 1278
Joined: Thu Nov 29, 2007 9:46 am

Re: Report from Query with UNION

Post by Sliderule »

You are using MySQL ( per the note at the bottom of your post ) . . . and . . . that is fine.

Which Report engine are you using . . .
  1. Oracle Report Builder ( ORB ) ?
  2. Writer ?
Assuming you are using ORB,
  1. From the Navigator ( F5 )
  2. Pressing, Report at the top
  3. From the Data tab
  4. If you 'insert' the SQL, AND, for, Analyze SQL command . . . choose . . . No

    this means, it will take any SQL you generate and send it directly to the database engine
I hope this helps, please be me / us know.

Sliderule

Thanks to add [Solved] in your 1st post Subject (edit button top right) if this issue has been resolved.
One Big Yawn
Posts: 24
Joined: Sat Jul 14, 2012 6:09 am

Re: Report from Query with UNION

Post by One Big Yawn »

I am using ORB.

I'm not sure I completely understand step 4, but I set up the Data tab as
Content Type SQL Command
Content pasted SQL Code from OP
Analyze SQL command No
Report Output ODF Text

Now, when I try to open the report, I get an error
You have an error in your SQL syntax; check that the manual corresonds to your MySQL server version for the right syntax to use near ';) AS "_LibreOffice_report_result"' at line 30
LibreOffice 3.5, MySQL 5.5, Windows 7
User avatar
Sliderule
Volunteer
Posts: 1278
Joined: Thu Nov 29, 2007 9:46 am

Re: Report from Query with UNION

Post by Sliderule »

One Big Yawn wrote:I am using ORB.

I'm not sure I completely understand step 4, but I set up the Data tab as
Content Type SQL Command
Content pasted SQL Code from OP
Analyze SQL command No
Report Output ODF Text

Now, when I try to open the report, I get an error
You have an error in your SQL syntax; check that the manual corresonds to your MySQL server version for the right syntax to use near ';) AS "_LibreOffice_report_result"' at line 30
OK, the SQL you pasted above, at the very END, contains a semi-colon ( ; ) . . . therefore, paste the same thing in . . . but . . . withOUT the semi-colon.

I hope this helps, please be sure to let me / us know.

Sliderule

Thanks to add [Solved] in your 1st post Subject (edit button top right) if this issue has been resolved.
One Big Yawn
Posts: 24
Joined: Sat Jul 14, 2012 6:09 am

Re: Report from Query with UNION

Post by One Big Yawn »

Thanks for your advice, but I'm getting the same error without the semi-colon, except now it cites
near ' " __LibreOffice_report_result" ' at line 30
LibreOffice 3.5, MySQL 5.5, Windows 7
User avatar
Sliderule
Volunteer
Posts: 1278
Joined: Thu Nov 29, 2007 9:46 am

Re: Report from Query with UNION

Post by Sliderule »

One Big Yawn wrote:Thanks for your advice, but I'm getting the same error without the semi-colon, except now it cites
near ' " __LibreOffice_report_result" ' at line 30
In that case . . . the error, probably is in the SQL ( query ) you wrote. So, time to see if just the SQL ( query ) will run, and, where the error is.

Steps to follow:
  1. Start your OpenOffice Base file
  2. On the left, click on the Queries icon
  3. Under Tasks, click on Create Query in SQL View...
  4. Copy and paste the SQL you created
  5. On the toolbar, click once on the icon with the green check mark and the letters SQL
  6. Run the Query ( F5 or Run Query icon on the toolbar )
  7. Review and correct any error message you see :crazy:
  8. Save the Query to a name of your choice
  9. In your Report, choose your correct / saved Query, by name, rather than the 'raw' SQL ( the reason is you should now have 'clean / correct' SQL :super: )
I hope this helps, please be sure to let me / us know.

Sliderule

Thanks to add [Solved] in your 1st post Subject (edit button top right) if this issue has been resolved.
One Big Yawn
Posts: 24
Joined: Sat Jul 14, 2012 6:09 am

Re: Report from Query with UNION

Post by One Big Yawn »

The SQL checks out alright if I run it directly (though, there is no green check in the LibreOffice version) in Base. It also runs fine directly in the MySQL command prompt and SQLyog, so I think the code itself is fine.

The report I am currently troubleshooting is from a query input the same way you advised above, created w/ the report wizard. If I create the report manually it seems to work out. Though if I use the wizard, it seems to create the report w/ the error messages as described above if I create it as dynamic. If I create it as static, it doesn't even create the report....

So, it seems as though the Report Wizard has some bugs and that is the issue. I'll declare this as solved, I suppose.

Thanks for the help.
LibreOffice 3.5, MySQL 5.5, Windows 7
One Big Yawn
Posts: 24
Joined: Sat Jul 14, 2012 6:09 am

Re: Report from Query with UNION and parameters?

Post by One Big Yawn »

Fogive me for un[solving] this. I don't know proper board etiquette for this, but I am trying to incorporate parameters into the original query.

To do this, I remove all aliases to allow the query to be run through Base instead of native SQL. I also cannot use UNION, so I attempt creating a view as outlined here:
http://user.services.openoffice.org/en/ ... 00#p198621

However, I get an error because views are made into the backend, which doesn't support :parameters. Has a workaround for this been developed?
LibreOffice 3.5, MySQL 5.5, Windows 7
User avatar
Sliderule
Volunteer
Posts: 1278
Joined: Thu Nov 29, 2007 9:46 am

Re: Report from Query with UNION and parameters?

Post by Sliderule »

That is correct, a View is part of your database back-end ( per your post above, MySQL 5.5 ).

Therefore, the solution is still easy, with a few SMALL changes. :bravo:
  1. Create your View, from your UNION Query, but, withOUT . . . repeat . . . withOUT any part of a parameter Query . . . that is . . . withOUT the colon ( : ).
  2. Now, the newly created View will show up as a part of the Tables list ( database back-end ).
  3. Now, create a new Query from the newly created View, and, now, with the new Query you can include the Parameter Prompt(s) with the colon ( : ).
Conclusion: Best of both worlds, you have a View that brings back your data ( with the UNION clause ), and, the Query from that View can bring back only the desired records.

Sliderule

Thanks to add [Solved] in your 1st post Subject (edit button top right) if this issue has been resolved.
One Big Yawn
Posts: 24
Joined: Sat Jul 14, 2012 6:09 am

Re: Report from Query with UNION and parameters?

Post by One Big Yawn »

Thanks...ALMOST there. I can query the new view w/ the unions and add contraints.

However, when I try to change these constraints to :parameters, I get an error
No value specified for parameter 1.
I got this initially playing around, and it had to do w/ the name of the parameter, ":date". That doesn't seem to be the case here, as I've tried several names.

Everything I'm finding online says this is an older bug that should be taken care of in the current version of Base.
LibreOffice 3.5, MySQL 5.5, Windows 7
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Report from Query with UNION and parameters?

Post by Villeroy »

Works for me with a HSQLDB backend.
Attachments
union_view_query.odb
param query from union view
(4.33 KiB) Downloaded 388 times
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
User avatar
Sliderule
Volunteer
Posts: 1278
Joined: Thu Nov 29, 2007 9:46 am

Re: Report from Query with UNION and parameters?

Post by Sliderule »

Using a Parameter, for an input from the end user of :date will result in an error, because, date is a database reserved word. A simple work-around is using :Enter_date instead.

As a general rule, I prefer to start all of my parameters with Enter_ for two reasons:
  1. No violation or concern with database reserved words
  2. Since, the parameters are presented to the end user in 'alphabetical order' . . . by using . . . Enter_ followed by a number and another underscore . . . for example:
    1. :Enter_01_From_Date
    2. :Enter_02_To_Date
    3. :Enter_03_First_Name
    4. :Enter_04_Last_Name
will allow presentation to the end user in MY desired sequence, rather than just an alphabetical sequence :super: .

If you really want some assistance here, in addition to what Villeroy provided ( thank-you Villeroy :bravo: ), please present the actual SQL statement ( Query ) you have created against your working View.

Sliderule

Thanks to add [Solved] in your 1st post Subject (edit button top right) if this issue has been resolved.
One Big Yawn
Posts: 24
Joined: Sat Jul 14, 2012 6:09 am

Re: Report from Query with UNION and parameters?

Post by One Big Yawn »

Thanks a lot guys for all your help, and it is working now.
It had nothing to do w/ the date... I just had to close it and reopen it for it to 'rewrite' itself in Base SQL, or whatever you'd like to call it.

That being said, I still have one more issue... my WITH ROLLUP clause is not coming through.

I am running my queries as such:

View 1

Code: Select all

SELECT BenID, CONCAT_WS(',', BenSName, BenFName) NAME, DOB, Gender Sex, Island,
FortnightRate Amount, BankNo, BankSuff, DirsignDate, Ben1EffDate
FROM Beneficiary b, Apply a, BenefitType bt
WHERE b.BankNo IS NOT NULL
AND b.benid = a.beneficiary
AND a.benefit1 = bt.bentype
AND bentype LIKE '%Child%'
AND Ben1Active = 'Active'
UNION
SELECT BenID ben, CONCAT_WS(',', BenSName, BenFName) NAMES, DOB bod, Gender, Island place,
FortnightRate, BankNo NO, BankSuff suf, dirsigndate, ben2effdate
FROM Beneficiary b, Apply a, BenefitType bt
WHERE b.BankNo IS NOT NULL
AND b.benid = a.beneficiary
AND a.benefit2 = bt.bentype
AND bentype LIKE '%Child%'
AND Ben2Active = 'Active';
Since MySQL doesn't allow subqueries for views, I did a view of view 1 as such:

Code: Select all

CREATE VIEW MVParaUnRoll AS SELECT IFNULL(Island, 'Grand Total') Location,  IFNULL( NAME, CONCAT_WS(' ', Island, 'Total')) Person, IFNULL(BenID,0) ID, IFNULL(DOB, '->') Born, IFNULL(Sex, '->') 'M/F', DirS
ignDate, Ben1EffDate, IFNULL(BankNo, '->') Bank, IFNULL(BankSuff, '->') Suff, COUNT(BenID) ID_Total, FORMAT(SUM(Amount),2) 'Total'
FROM MVParamTest
GROUP BY Island, Name WITH ROLLUP;
This looks fine on the command line prompt of MySQL (though, I should probably change my IfNulls to CASE WHENs, and change the name of my 'M/F' column). I run it in Base with this query:

Code: Select all

SELECT Location, Person, ID, Born, "M/F", Bank, Suff, ID_Total, Total
FROM MVParaUnRoll
 
It also looks fine, until I add contraints to the query in either the back or front end and as parameters or dates: this appears to be an SQL issue.

I add 1 constraint such as:

Code: Select all

WHERE DirSignDate > 2012-07-01
Here, it looks about how it should, both with or without parameters, but I do get a MySQL Warning:
Warning 1292 Incorrect date value: '2004' for column 'DirSignDate' at row 12
When I look at the DirSignDate for row 12, it's actually a subtotal row for an island and should be null, but because of bugs in MySQL, gets the DirSignDate of the row immediately above it (which I can live with). Moreover, the DirSignDate for that row is not '2004' and is in a valid date format. All I can guess is that maybe MySQL is trying to ROLLUP the dates for that island? Even if this is the case, it would be doing so incorrectly.

If I add another contstraint

Code: Select all

AND Ben1EffDate > 2012-07-01
I get two of the same warnings for each constraint on the same row. Now, however, It reports the next row (13), and then stops... leaving out 1 more row of data, 1 more subtotal, and the grand total, which would be a dealbreaker.

I'd like to mark this as solved, since it seems to be more of a MySQL issue w/ views than Base, but I'm only doing views as a workaround for Base.
You guys have been a great help. I'm very grateful I learned SQL on Oracle instead of MySQL, though.
LibreOffice 3.5, MySQL 5.5, Windows 7
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Report from Query with UNION and parameters?

Post by Villeroy »

Try double-quoted names of field, tables and aliases together with literal dates in single quotes.

"Ben1EffDate" > '2012-07-01'
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
One Big Yawn
Posts: 24
Joined: Sat Jul 14, 2012 6:09 am

Re: Report from Query with UNION and parameters?

Post by One Big Yawn »

That fixed it... in MySQL. :crazy:

Running it in Base yields the same outcome as outlined above w/ it being fine w/ the DirSignDate constraint and only 13 with the Ben1EffDate.

It is specifically with Ben1EffDate. If I run the query in BaseSpeak as:

Code: Select all

SELECT `Location`, `Person`, `ID`, `Born`, `M/F`, `Bank`, `Suff`, `ID_Total`, `Total`, `DirSignDate` FROM `test`.`mvparaunroll` AS `MVParaUnRoll` WHERE "Ben1EffDate" > '2012-07-01'
w/ 13 lines returned (no grand total, etc.) in Base.

Running the same code in MySQL:

Code: Select all

SELECT Location, Person, ID, Born, "M/F", Bank, Suff, ID_Total, Total, DirSignDate
FROM MVParaUnRoll
WHERE "Ben1EffDate" > '2012-07-01';
returns 16 lines w/ no warnings, so it is not an SQL issue.

I'll try remaking the views with quotes. It isn't a datatype issue, because they're both dates. All I can guess is it has something to do with the UNION and Ben1EffDate not properly merging with Ben2EffDate for Base.
LibreOffice 3.5, MySQL 5.5, Windows 7
One Big Yawn
Posts: 24
Joined: Sat Jul 14, 2012 6:09 am

Re: Report from Query with UNION and parameters?

Post by One Big Yawn »

Got It!

Had nothing to do w/ names or anything. I had placed a NULL value in the Ben2EffDate for testing and I guess it was too much for Base.

At the very least, this thread will serve as thorugh documentation for anyone looking for it in the future.

Thanks a lot for all your help guys.

:bravo:
LibreOffice 3.5, MySQL 5.5, Windows 7
Post Reply