IF (or IIF) not working in Liberoffice Base?

[urbanian]

I have a table with a date for an event, and for esoteric reasons I want to flag if the event happened in the first quarter of the year. Should be simple, right?

(Background, the esoteric reason is that I need to treat the first quarter of the year as part of the previous year. Sort of like an odd fiscal year.)

Pulling the month out of the date is easy. Example query:

SELECT “StartDate” AS “ADate”, MONTH( “StartDate”) AS “Result” FROM “TabBills”

And I can do arithmetic calculations on the month:

SELECT “StartDate” AS “ADate”, MONTH( “StartDate” ) + 1 AS “Result” FROM “TabBills”

(Not that that’s useful, but it shows at least some calculation is allowed.)

But any logic-based operation fails. For example:

SELECT “StartDate” AS “ADate”, (MONTH( “StartDate” ) < 4) AS “Result” FROM “TabBills”

(With or without the parens) I’d expect to get a TRUE for the first quarter and FALSE after. Instead I just get Syntax error in SQL statement.

I also tried

SELECT “StartDate” AS “ADate”, MONTH( “StartDate” ) IN (1, 2, 3) AS “Result” FROM “TabBills”

No joy there, either. I’m obviously missing something fundamental. Any suggestions for how to do this?

I’d hate to have to join to a table that has 12 rows:
1 TRUE
2 TRUE
3 TRUE
4 FALSE
…

[RobertG]

Please download the Base Guide: BG73-BaseGuide.pdf - The Document Foundation Nextcloud
Seems you are using internal HSQLDB (MONTH(“StartDate”) won’t work with Firebird). So you coula also use QUARTER("StartDate").
And you are missing CASE WHEN … THEN … ELSE … END.

[Wanderer]

You could use IF while connected to a MySQL-Database or MS SQL-Server.
.
Base itself is no Database, it connects to databases. And available syntax depends on the database.

[CRDF]

@Wanderer

while connected to a MySQL-Database or MS SQL-Server.

But as I can remember now, in SQL Server could not
SELECT month(StartDate) < […]

[Villeroy]

Embedded HSQL: SELECT "TabBills".*, (Month("StartDate")-1)/3 +1 AS "Quarter" FROM "TabBills"
https://www.hsqldb.org/doc/1.8/guide/ch09.html

Firebird3: SELECT "TabBills".*, (EXTRACT(MONTH FROM "StartDate")-1)/3 +1 AS "Quarter" FROM "TabBills"
https://www.firebirdsql.org/file/documentation/html/en/refdocs/fblangref30/firebird-30-language-reference.html#fblangref30-functions-datetime

[urbanian]

Wow, I didn’t realize that this was going to go off into the weeds of different database servers. Yes, I know that Base is really a front-end to a database server, but it comes with HSQLDB and assumed that would have features similar to (maybe limited) other SQL implementations. In particular calculations and things like IF(,) and CASE would work the same.

Silly me.

I’m surprised that Firebird is so different from others. But this is an aspect of databases that I’m completely ignorant of.

For now, think I might be able use something similar to @Villeroy’s suggestion to calculate “Net Billing” year for our solar panels. Or otherwise explore other MacOS-compatible SQL servers. It should be easier using QUARTER() instead of MONTH(). Or the doc @RobertG pointed me to leads me to think CASEWHEN() may be what I want. (Why call it that instead of IF or IIF?)

If that works it will be on to reports, something I haven’t looked into in Base yet. Can I reproduce my FileMakerPro reports? We’ll see. It should would be nice to not have to shell out u$s625 for a current FileMaker Pro individual license.

Anyway, thanks for all of the responses.

[Villeroy]

odbc-jdbc-guide.pdf

[Villeroy]

Other variant with Firebird.

SELECT "D", 
CASE WHEN EXTRACT(MONTH FROM "D") IN(1,2,3) THEN 1  
     WHEN EXTRACT(MONTH FROM "D") IN(4,5,6) THEN 2 
     WHEN EXTRACT(MONTH FROM "D") IN(7,8,9) THEN 3  
     ELSE 4 
END AS "Quarter" 
FROM "Data"

Same works with HSQL if you replace extract(…) with month(…)

[urbanian]

It seems that this works:

YEAR( “StartDate” ) - CASEWHEN( QUARTER( “StartDate” ) = 1, 1, 0 )

Could have used MONTH(…) < 4, too. And IIF is the Firebird equivalent.

[Villeroy]

HSQL has a QUARTER function, indeed. I searched the HSQL documentation for “quarter” and did not get any match, possibly due to a typing error.
Firebird’s EXTACT definitively does not support quarters of a year:

EXTRACT (<part> FROM <datetime>)
<part> ::=
YEAR | MONTH | WEEK
| DAY | WEEKDAY | YEARDAY
| HOUR | MINUTE | SECOND | MILLISECOND
<datetime> ::= a DATE, TIME or TIMESTAMP expression

[CRDF]

@urbanian

Any suggestions for how to do this?

My first suggestion is to remake your DB step by step using Firebird not embedded.
HSQLDB is an excellent DB, but version shipped with Base is an old one. LibreOffice did not update it.
And after that, you must check available sintaxes for this DB engine, of course.