[Solved] Oracle Report Grouping not using alias in report

[Jimbes]

I'm brand new to OO Base. I've been going through the tutorial by Mariano Casanova at this URL https://wiki.documentfoundation.org/ima ... torial.pdf. There have been a few things that have changed, but I've managed to work my way through them (for example MONTH now needs to be 'mm' in a DATEDIFF). However, I've spent many hours trying to get the report generator to work for a grouping. Following the example, I have Patients with multiple phone numbers. I'm trying to create a report and group by Patient and Description (phone type - like "cell", "home", etc.). The tutorial pages for this are from 135-138. The SQL query uses an alias called "Patient Name", and the SQL creates the output just fine, without the grouping of course. When I use either the Report Wizard, or Design, the generator is not using my "Patient Name" alias, it's substituting "Patient"."Patient Name" which doesn't exist.

This is the query:
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";

The screenshot shows the pop-up I get when I try to run the report.

I'm using OO Base 4.0 (even though my sig would not let me put 4.0 in it to register). I am on a Mac running 10.8.4.

I don't think I'm doing something wrong, but I imagine I may have to ditch the alias or something to work around what to me, seems like a bug. Any help will be greatly appreciated.

Thanks,
Jim

[DACM]

The Oracle Report Builder is problemmatic.

See this thread: http://forum.openoffice.org/en/forum/vi ... 32#p272432

And note that GROUP_CONCAT is available with an engine upgrade.
...

[Sliderule]

Jim:

According to the error message ( thank-you for including it EXACTLY as it ( the database back-end, that is, the database engine, HSQL reported ) . . . the problem / solution is easy, and, it has NOTHING to do with the Oracle Report Writer Grouping.

I am, reproducing, the database request ( SELECT statement ) you showed in the graphic, but, it has line breaks, to make it more easy to see and understand.

Code: Select all

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"."Patient Name",
         "Phone Number"."Description"

Above, in the ORDER BY clause, you are referencing a field in your database table ( "Patient" ) . . . BUT . . . that field ( column ) does NOT exist in your database table ( "Patient"."Patient Name" ) . . . and . . . that is exactly what the error message says.


Solution . . . the following SELECT statement:

Code: Select all

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",
         "Phone Number"."Description"

Explanation: In the above, the ORDER BY can use the calculated value by the name "Patient Name" . Since, it is a calculated field, and, it is NOT a part of the table, you do NOT reference it as a field in the table.

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

Sliderule

Thanks to ad [Solved] in your 1st post Subject (edit buton top right) if this isue has ben resolved.

[Jimbes]

Sliderule, Thanks for your post. However, the thing is, I don't put that in my query. I did post it in the original message, but maybe you just overlooked it. Here it is again:

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";

This is the query I created. Then I go to report generator and create a report based on that query. In the generator wizard, I am trying to sort on "Patient Name" and "Description". When the report generator wizard finishes and I try to run the report, THAT'S when I see that attachment I added. The REPORT GENERATOR is adding that "Patient"."Patient Name" field, instead of using the alias from my query. Actually, it's using my alias "Patient Name", but it's prepending the "Patient" table name, which I don't think it should. I've tried to do the ordering myself, but can't seem to get that working in the report either.


Jim

[Sliderule]

Jim:

Then I go to report generator and create a report based on that query.

And, that is why, I do NOT use the Wizard ( Report Generator ) to create the report. Instead, use . . . Create Report in Design View... so you give it the:

1. Table
2. View
3. Query
4. or, SQL Select statement

to use for the Report.

Sliderule

Thanks to ad [Solved] in your 1st post Subject (edit buton top right) if this issue has ben resolved.