[Solved] "Newbie to Advocate" problem

Getting your data onto paper - or the web - Discussing the reports features of Base

[Solved] "Newbie to Advocate" problem

Postby NotYetSenile » Sun Apr 19, 2015 10:26 am

I've been going through this great tutorial and am finally near the end - Reports section. After following the instructions I get to that wonderful step - execute report. No go! Base has inserted "Patient"."Patient Name" into my ORDER BY "Patient Name" ASC step, so it reads -
ORDER BY "Patient"."Patient Name", "Patient Name" ASC
and flags an error (naturally).
Running the basic query works fine. What could I have done wrong?
(I'm tempted to suggest a bug but every time I've found a bug in this tutorial it has turned out to be a mental lapse on my part.)
Last edited by NotYetSenile on Wed Apr 22, 2015 7:11 am, edited 1 time in total.
OpenOffice V4.1.1 on Windows 7
NotYetSenile
 
Posts: 20
Joined: Tue Feb 24, 2015 8:10 am

Re: "Newbie to Advocate" problem

Postby Villeroy » Sun Apr 19, 2015 12:01 pm

Please post (paste) the full SQL statement.
Tell us about your database software (embedded HSQL or something better?).
Tell us about your reporting software (Oracle Report Builder, plain table report or query dump into spreadsheet?)
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, OpenOffice 4.x & LibreOffice 6.x
User avatar
Villeroy
Volunteer
 
Posts: 26410
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: "Newbie to Advocate" problem

Postby NotYetSenile » Mon Apr 20, 2015 5:48 am

Yes, I should have thought of that.
1. My original SQL statement (copied from the tutorial) -
SELECT CONCAT( CONCAT( "Patient"."Surname", ', ' ), "Patient"."First Name" ) AS "Patient Name", "Phone Number"."Number", "Phone Number"."Description" FROM "Patient", "Phone Number" WHERE "Patient"."ID Number" = "Phone Number"."Patient ID" ORDER BY "Patient Name" ASC
I tried changing the concatenation result to "Patient Full Name" and the ORDER BY likewise. Same result. (There are a few misprints in the tutorial where the names are not exactly the same as those in the database.)
When I looked at the SQL statement in the error message, it read {......ORDER BY "Patient"."Patient Name" "Patient Name" ASC}
Last but not least, I removed the ORDER BY clause altogether.
This time the SQL error statement read {....ORDER BY "Patient"."Patient Name" ASC} !!?
(Sorry. As I write this, I realise I should have closed and re-opened the DB before trying that. Base seems to have a habit of not deleting stuff fully on the fly.)
Finally, I tried ordering by "Description". Worked like a charm, although the output format was a bit messy.

2. Embedded HSQL

3. Oracle Report Builder
OpenOffice V4.1.1 on Windows 7
NotYetSenile
 
Posts: 20
Joined: Tue Feb 24, 2015 8:10 am

Re: "Newbie to Advocate" problem

Postby Villeroy » Mon Apr 20, 2015 2:24 pm

I don't use the Oracle Report Builder because I find it too complex for my simple requirements and too buggy at the same time.

What I would try:

1. Remove the ORDER BY clause from the query and let the report builder take care of the sorting.
2. Keep the ORDER BY clause in the query and don't let the report builder take care of the sorting.
3. Save the query with "direct SQL mode" and see if it makes any difference (SQL editor's green [SQL] button or menu:Edit>"Run SQL directly).
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, OpenOffice 4.x & LibreOffice 6.x
User avatar
Villeroy
Volunteer
 
Posts: 26410
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: "Newbie to Advocate" problem

Postby NotYetSenile » Wed Apr 22, 2015 7:10 am

Thanks, Villeroy.
My mistake #1. Misinformation. I was using the Wizard, not Oracle Report Builder. BUT ...
It seemed that, even though I was using the Wizard, ORB got in the act as soon as I executed the report. I removed ORB and all was well. Needles to say, I won't be re-installing it! (I had noticed a lot of error complaints about ORB on the forum but they were all old so I guessed it had been fixed. Wrong!)
OpenOffice V4.1.1 on Windows 7
NotYetSenile
 
Posts: 20
Joined: Tue Feb 24, 2015 8:10 am


Return to Reporting

Who is online

Users browsing this forum: No registered users and 1 guest