Ask Your Question
0

HSQLDB SQL in Firebird

asked 2018-08-23 10:53:14 +0100

Zoarsk gravatar image

Hi

I have been getting to grips with Base, coming from a MS Access background. I have just installed 6.1 which has “Firebird” as a SQL engine option, and now I’m stuck on something simple.

I have a number of queries that need converting, the simplest example is below. In it’s HSQLDB format

SELECT Top 10 "ClassA", "ClassB", "ClassC", "Date", "ClassA" + "ClassB" + "ClassC" AS "Total" FROM "tblData" ORDER BY "Total" DESC, "Date" ASC

this works fine.

In what I believed is the correct Firebird form:

SELECT First 10 "ClassA", "ClassB", "ClassC", "Date", "ClassA" + "ClassB" + "ClassC" AS "Total" FROM "tblData" ORDER BY "Total" DESC, "Date" ASC

So changed “Top” to “First”. The issue is the Firebird SQL runs fine in design mode “F5”, however if I create a form, it runs and returns the top (First) 10 records correctly and in order but there is no value in the “Total” column.

Obviously done something wrong but for the life of me I can’t find an answer, any help appreciated.

edit retag flag offensive close merge delete

1 Answer

Sort by » oldest newest most voted
0

answered 2018-08-23 17:14:44 +0100

Ratslinger gravatar image

updated 2018-08-25 19:33:03 +0100

Hello,

Don't believe it is you doing something wrong. My testing shows error but in a different sense. If your field type is of Numeric or Decimal then the problem may be in Base. See -> Bug #118043.

My test was selecting field1 + field2 = field3 (350.00 + 1650.00 resulting in 200000) using Firebird embedded in LO v6.1.0.3 but the same select produced a correct result in Firebird 3 server (2000.00).

BTW - Firebird embedded has been available (experimental) for some time now, not just v6.1

Edit 2018-08-25:

Have performed some further testing on this. The table defs were field type numeric 12,2 and a select on the individual fields were correct. As mentioned above simple math reflected an incorrect result. However, if the math is cast as numeric it works:

SELECT Field1, Field2, cast(Field + Field2 as decimal(12,2)) as Result FROM MyTable

A cast to numeric or decimal produced the correct results.

edit flag offensive delete link more

Comments

Thanks for the reply, at least I'm not going mad then. Guess I will have to stick with HSQLDB for now, I wonder if they will ever fix it, unlike the Calc Bugs that seem to have been there for ever. Cheers

Zoarsk gravatar imageZoarsk ( 2018-08-24 08:14:15 +0100 )edit

@Zoarsk As another alternative, as I have done for months now, you can use Firebird 3 server (my alternative - now moving from MySQL to PostgreSQL). You can modify everything in preparation for embedded and even use DB's in embedded now. Fairly easy to move over.

Have even successfully ported a fair size MySQL DB to Firebird embedded through Firebird 3 server. Side by side running for months (daily use) and no problems.

Ratslinger gravatar imageRatslinger ( 2018-08-25 05:22:08 +0100 )edit

I may look at that, although the current work is corporate, is LO a viable alternative to well known commercial Office product, that will be a NO then. You don’t happen to be any good at LO Macros in Base? Have got so far but now stuck on how to write data back to table. Anyway thanks for your help

Zoarsk gravatar imageZoarsk ( 2018-08-26 12:30:10 +0100 )edit

@Zoarsk As for LO being a viable alternative, it always depends upon requirements.

Have worked with Base (and other LO areas) using macros for some time. There are many others in this forum with such experience. You should present this as a new question and please include specifics about environment (LO version, OS, DB, etc.). What code you have that is not working for you will also be of help.

Ratslinger gravatar imageRatslinger ( 2018-08-26 17:19:28 +0100 )edit
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2018-08-23 10:53:14 +0100

Seen: 266 times

Last updated: Aug 25 '18