We're currently migrating from Ask to Discourse, read the details here

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 +0200

LO_Fans gravatar image

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

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 +0200

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 +0200 )edit

Question Tools

1 follower


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

Seen: 128 times

Last updated: Feb 23 '20