SQL Code Syntax for IIF

[DMG]

Hello. I am learning LibreOffice Base version 7.5 (64k), with an HSQLDB embedded database, on a Windows 10 desktop.

My database was imported from an MS Access database. My ACCESS database query contains the following line in the SQL Code. I want to duplicate this in my BASE query, but I’m getting a Syntax Error:

IIf(IsNull([fldTimeToPrepare]),"","Prep Time: " & [fldTimeToPrepare]) AS PrepTime, 

If the field “fldTimeToPrepare” is empty, I want the report to display nothing… otherwise, display the concatenated phrase “Prep Time: (time)”

Does anybody know what’s wrong with my SQL code for this field? Does BASE use a different syntax?

THANKS!

[Villeroy]

http://www.hsqldb.org/doc/1.8/guide/ch09.html

COALESCE('Prep Time:' || "fldTmeToPrepare", '')AS "Preptime"

However, RobertG is right. The concatenation with Null returns Null anyway.

[RobertG]

@DMG

IIf(IsNull([fldTimeToPrepare]),"","Prep Time: " & [fldTimeToPrepare]) AS PrepTime,

IIf is only available for internal Firebird, not for internal HSQLDB. For HSQLDB use
CASEWHEN ("fldTimeToPrepare" IS NULL,'','Prep Time:'||"flsTimeToPrepare") AS "PrepTime",
But you could also set only

'Prep Time:'||"flsTimeToPrepare" AS "PrepTime",

because this will set “PrepTime” to NULL if the field “flsTimeToPrepare” is NULL.

[DMG]

THANK YOU, @RobertG and @Villeroy !
.
All these variations worked PERFECTLY for me, and I was able to convert almost all my field concatenations to the new BASE query.
.
I also found the HSQLDB.org guide very interesting… so I’ll keep the link handy.
.
The only statement I’ve still having trouble with is the following:

	, CASEWHEN ("fldType_SouthBeachDiet"=TRUE,"fldType_SouthBeachDietPhaseNo",'') AS "Message2"

In this example, fldType_SouthBeachDiet is a Yes/No checkbox. So, if true, I want the result to display the Phase # (a numeric field); otherwise, nothing.
.
But this code returns an ERROR that says “Wrong Data Type.”
SQL Status: 37000 Error code: -16
Wrong data type: java.lang.NumberFormatException: For input string: “” in statement [SELECT…
.
How do I designate that “fldType_SouthBeachDietPhaseNo” is a numeric field??
.
THANKS!

[RobertG]

@DMG

Wrong data type: java.lang.NumberFormatException: For input string: “”

There is expected a number, not an empty string. Set NULL instead.

[DMG]

@DMG

, CASEWHEN ("fldType_SouthBeachDiet"=TRUE,"fldType_SouthBeachDietPhaseNo",NULL) AS "Message2"

PERFECT, @RobertG Thank you!