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

[LO_Fans]

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?

[Ratslinger]

Hello,

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).

[LO_Fans]

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.