Ask Your Question

Why parameter query can be used as input in Libreoffice Base 6.4 HSQLDB 1.8 but not in Firebird 3.0? [closed]

asked 2020-02-23 12:13:09 +0100

LO_Fans gravatar image

updated 2020-07-09 00:46:36 +0100

Alex Kemp gravatar image

Hi, I'm not sure I put the right title since I'm not a programmer. Sorry for that.

In Libreoffice Base 6.4 HSQLDB 1.8, this query works well:

SELECT "ID", "DeviceName", :InputYear - Year("PurchaseDate") as "DeviceAge" From Table1

So, I am using parameter query :InputYear to calculate "Device Age" in a query. This is useful to calculate yearly depreciation value of my equipments.

In Libreoffice Base 6.4 Firebird 3.0, I've made small adjustment:

SELECT "ID", "DeviceName", :InputYear - Extract (Year from "PurchaseDate") as "DeviceAge" From Table1

but did not work. The error message is: Firebird_sdbc error, SQL error code = -804, Data type unknown

What went wrong?

edit retag flag offensive reopen merge delete

Closed for the following reason the question is answered, right answer was accepted by LO_Fans
close date 2020-03-02 03:47:50.905366

1 Answer

Sort by » oldest newest most voted

answered 2020-02-23 22:59:20 +0100

Ratslinger gravatar image


This does not appear to be a function within Firebird - embedded or server. Can be done with PSQL but that does not appear to be efficient in your case. You may want to consider placing that entry in a lookup table (just a single record table to hold information) and use that in your statement. Same concept as when using a table filter.

As an aside, did test with PostgreSQL and had no problem (JDBC connection).

edit flag offensive delete link more


Thank you @Ratslinger. It's a pity I don't understand how to do it with PSQL. So, I use macro instead to rewrite the Query's SQL.

LO_Fans gravatar imageLO_Fans ( 2020-03-02 03:47:32 +0100 )edit

Question Tools

1 follower


Asked: 2020-02-23 12:13:09 +0100

Seen: 84 times

Last updated: Feb 23