Ask Your Question
0

Report builder, sql and Firebird

asked 2019-03-27 07:57:12 +0100

johnwt gravatar image

I have a small database which was in HSQLDB and I have now converted to Firebird in LO 6.1.5.2 from Debian Testing. The sql used by Firebird is different from HSQLDB and I have modified a query so that it now works with Firebird. However, when I run a report using Report Builder and data from the query, data from computed fields are not being included. I see from https://forum.openoffice.org/en/forum... that "When Base makes a report in LibreOffice, or OpenOffice with the Report Builder extension, it runs the query through the Base parser, which can't handle some SQL." With LO now moving to Firebird what sql will Report Builder now accept? Is there any documentation anywhere?

edit retag flag offensive close merge delete

Comments

Hello,

The provided link is not relevant to the question presented. Have just created a Report in Base using a query with calculated fields from a Firebird embedded DB. The only problem is a known bug regarding misplaced decimal (fixed with a CAST).

Please present more information on your problem - Sample would be best.

Ratslinger gravatar imageRatslinger ( 2019-03-27 15:39:46 +0100 )edit

This is one line in the query:CASE "Month" WHEN 1 THEN 'January' WHEN 2 then 'February' WHEN 3 then 'March' WHEN 4 then 'April' WHEN 5 then 'May' WHEN 6 then 'June' WHEN 7 then 'July' WHEN 8 then 'August' WHEN 9 then 'September' WHEN 10 then 'October' WHEN 11 then 'November' WHEN 12 then 'December' END || ' ' || "Day" || "Dates" "Fulldates", It gives the expected output when I run the query but nothing appears where "Fulldates" is included in the report.

johnwt gravatar imagejohnwt ( 2019-03-27 18:00:25 +0100 )edit

In the query, is Run SQL command directly turned on?

Ratslinger gravatar imageRatslinger ( 2019-03-27 18:39:58 +0100 )edit

I see a button to Run SQL command directly and the same item in the Edit menu when in the query design panel. If I click on either of these it changes the Switch Design View On/Off button to greyed out and back. Is Run SQL command directly turned off if the Switch Design View On/Off button is greyed out? If I try to exit the edit query panel with the Switch Design View On/Off button not greyed out I get a syntax error.

johnwt gravatar imagejohnwt ( 2019-03-28 08:02:48 +0100 )edit

The statement used does indeed require it to be run directly - no choice. With run directly turned on (from toolbar or menu) selecting Design View is not allowed.

Ratslinger gravatar imageRatslinger ( 2019-03-28 15:47:00 +0100 )edit

1 Answer

Sort by » oldest newest most voted
0

answered 2019-03-27 22:36:12 +0100

Ratslinger gravatar image

updated 2019-03-28 18:26:46 +0100

Hello,

There is a problem within Base, and not specific to just Firebird, dealing with alias names when Run SQL command directly is on. This affects both reports and forms displaying the data. Have seen differing degrees of when it works and when it doesn't.

Using just your example, you can just drop the alias name ("Fulldates") and in the use the generated name from the query (probably CONCATENATION).

Sub queries also present a problem:

 Select  "Total" from (SELECT  "Cost" * "Qty" "Total" FROM "Rental")

but :

Select  CAST("Total" AS NUMERIC(7,2)) from (SELECT  "Cost" * "Qty" "Total" FROM "Rental")

will give a usable field name ("CAST") which can be used in the report. It becomes increasingly difficult with multiple fields of this type becoming "CAST1" then "CAST2" and so on.

Now because of the limited information you have given, it is also possible there are other methods to obtain dates, Firebird certainly is more advanced than the HSQLDB embedded. There are also functions you can create & use. For example -> Functions with PSQL in Firebird 3. Have tried this one myself.

Edit 2019-03-28:

Have not touched your original report as I am not comfortable with it. Instead created a small sample report. If Report Wizard is used, get error list as stated. If created in design view seems to work OK. Sample has report TestAlias. Modified query Current concatenated by dropping Fulldaes alias.

Sample ------ EFIOS courses.odb

edit flag offensive delete link more

Comments

I tried dropping the alias name. I selected CONCATENATION, which was listed in the report builder editor, and I get a long set of error messages when I try to run the report, of which the most relevant one seems to be:

*no column name specified for column number 6 in derived table

I appreciate your suggestion about a date function, though it seems something of an overkill for what I am trying to do. However, I have another item in the query that has the same problem, not involving dates,

johnwt gravatar imagejohnwt ( 2019-03-28 17:31:34 +0100 )edit

Please post a scrubbed sample of the Base file.

Ratslinger gravatar imageRatslinger ( 2019-03-28 17:38:37 +0100 )edit

I've linked 1 file to this message:* EFIOS courses.odb (244 kB) hosted on Box: https://app.box.com/s/62ok5h38a47d6g5... There is nothing confidential in it. I use the report to produce the html for this page: http://dpets.uk/efios/efios/efioscour... (currently out of date while I sort this out).

johnwt gravatar imagejohnwt ( 2019-03-28 17:56:12 +0100 )edit

Will add edit to my answer.

Ratslinger gravatar imageRatslinger ( 2019-03-28 18:20:43 +0100 )edit

As soon as I turn on sorting and grouping the error messages come up.

johnwt gravatar imagejohnwt ( 2019-03-28 20:28:57 +0100 )edit

Probably best to file a bug report -> Bugzilla

Ratslinger gravatar imageRatslinger ( 2019-03-28 20:50:32 +0100 )edit

Done: https://bugs.documentfoundation.org/s... Thanks for your help.

johnwt gravatar imagejohnwt ( 2019-03-28 21:34:12 +0100 )edit
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2019-03-27 07:57:12 +0100

Seen: 224 times

Last updated: Mar 28