IIF Function from SQL to HSQLDB

Question

I have the following SQL statement in MS Access -

WITH 
    qryAwayMatches AS (SELECT MATCHTEAM.FOOTBALLMATCHID, MATCHTEAM.TEAMID, MATCHTEAM.GAMETYPE, MATCHPROTOCOL.MATCHTEAMID, MATCHPROTOCOL.GOALNUMBER, 
    MATCHPROTOCOL.YELLOWCARDNUMBER, MATCHPROTOCOL.REDCARDNUMBER FROM MATCHTEAM LEFT JOIN MATCHPROTOCOL ON MATCHTEAM.ID = MATCHPROTOCOL.MATCHTEAMID WHERE (((MATCHTEAM.GAMETYPE)='Away'))),
    qryHomeMatches AS (SELECT MATCHTEAM.FOOTBALLMATCHID, MATCHTEAM.TEAMID, MATCHTEAM.GAMETYPE, MATCHPROTOCOL.MATCHTEAMID, MATCHPROTOCOL.GOALNUMBER, 
    MATCHPROTOCOL.YELLOWCARDNUMBER, MATCHPROTOCOL.REDCARDNUMBER FROM MATCHTEAM LEFT JOIN MATCHPROTOCOL ON MATCHTEAM.ID = MATCHPROTOCOL.MATCHTEAMID WHERE (((MATCHTEAM.GAMETYPE)='Home'))),
    qryMatchResult AS (SELECT qryHomeMatches.FootballMatchID, qryHomeMatches.TeamID AS HomeTeamID, 
    qryAwayMatches.TeamID AS AwayTeamID, 
    qryHomeMatches.GoalNumber>qryAwayMatches.GoalNumber AS HomeTeamWin, 
    qryHomeMatches.GoalNumber=qryAwayMatches.GoalNumber AS NoWin, 
    qryHomeMatches.GoalNumber<qryAwayMatches.GoalNumber AS AwayTeamWin 
    FROM qryHomeMatches INNER JOIN qryAwayMatches ON qryHomeMatches.FootballMatchID = qryAwayMatches.FootballMatchID)
    SELECT Round.RoundNumber, Team.Name, **IIf([HomeTeamWin],3,IIf([NoWin],1,0)) AS Points** 
    FROM Round 
    INNER JOIN (Team INNER JOIN (RoundDetail INNER JOIN qryMatchResult ON RoundDetail.FootballMatchID = qryMatchResult.FootballMatchID) ON Team.ID = qryMatchResult.HomeTeamID) ON Round.ID = RoundDetail.RoundID;

When I try to execute this statement on button press in Java using an HSQLDB, I get the following error:

java.sql.SQLSyntaxErrorException: user lacks privilege or object not found: IIF

How can I solve this issue?

score 4 · Accepted Answer · answered May 14 '16 at 20:27

It seems from the documentation that HSQLDB doesn;t support IIF function, I can;t find it in the function's list:
http://hsqldb.org/doc/2.0/guide/builtinfunctions-chapt.html#bfc_general_functions

But a good news is: HSQLDB supports case expressions:
http://hsqldb.org/doc/2.0/guide/dataaccess-chapt.html#dac_query_expression

CASE

case specification

::= |

::= CASE ... [ ] END

::= CASE ... [ ] END

::= WHEN THEN

::= WHEN THEN

::= ELSE

::= |

::= [ { }... ]

::= | | | | | | | | | | | |

::= | NULL

::=

The case expression is definied by ANSII SQL standard and it is supported by most databases, while IIF function is proprietary and nonstandard.

You can rewrite:

iif ( condition, value_if_true, value_if_false )

using the case expression in this way:

CASE WHEN condition THEN value_if_true ELSE value_if_false END

for example:

SELECT CASE WHEN col1>1 THEN 20 ELSE 50 END
FROM ....

instead of

SELECT iif(col1>1,20,50)
FROM ...

At the moment there is also available: `CASEWHEN`. See http://hsqldb.org/doc/2.0/guide/builtinfunctions-chapt.html#bfc_general_functions — Michiel Leegwater, Nov 10 '22 at 16:17

IIF Function from SQL to HSQLDB

1 Answers1