3

community.

I have a quite typical task in SQLITE.

In case of X return Y, else Z.

For such purpose I was using CASE statement

"CASE WHEN X THEN Y ELSE Z END".

However, I found IIF function in official documentations

https://sqlite.org/lang_corefunc.html#iif

iif(X,Y,Z) The iif(X,Y,Z) function returns the value Y if X is true, and Z otherwise. The iif(X,Y,Z) function is logically equivalent to and generates the same bytecode as the CASE expression "CASE WHEN X THEN Y ELSE Z END".

But when trying to run such function, I get an error.

Execution finished with errors. Result: no such function: iif

What is the problem with this function? According to official documentation, such function should exist.

Alexander Tverdohleb
  • 433
  • 4
  • 13
  • 3
    [This answer](https://stackoverflow.com/a/61826915/1553090) on Stack Overflow says that function was introduced in SQLite 3.32.0. BTW, I found this by searching the web for "iif function sqlite" and it was one of the top results. – paddy Oct 12 '20 at 06:13
  • 2
    `case` is ANSI SQL standard and portable. – jarlh Oct 12 '20 at 08:28
  • Don't bother with `IIF()`. There is no reason to write code in SQLite that is backward compatible to MS Access. – Gordon Linoff Oct 12 '20 at 11:37

1 Answers1

3

Thanks @paddy and his link for this post IF() statement alternative in SQLite

The problem came from SQLite version, which should be 3.32 or higher. I've been using SQLite Browser, and sqlite version was 3.25. I updated Sqlite Browser and was able to use IIF function.

Alexander Tverdohleb
  • 433
  • 4
  • 13