error when using IFF in an update

(1.1) By 4xrasy (C17_4xrasy) on 2025-01-21 06:09:28 edited from 1.0 [link] [source]

I am designing a small database to keep track op passwords. All went well until this happened.

Error:

sqlite> update magdalena set password='1B.R#}yo.m@bd2FP>@J' where keyword='test1';
Parse error: no such function: IFF

I am using SQLite version 3.47.1 2024-11-25 12:07:48

The following trigger kills the cat …

/* Keeping track of historic passwords used
 *  
 * Increase the counter when a new password was created and archived in the HISTORY table.
 ********************************************************************************************/
CREATE TRIGGER IF NOT EXISTS HIS_COUNTER_UPDATE AFTER INSERT ON HISTORY
WHEN (SELECT entry_counter FROM HIS_COUNTER WHERE keyword == NEW.keyword) < 10
BEGIN
    
    INSERT OR REPLACE INTO HIS_COUNTER
    (keyword, entry_counter)
    VALUES (NEW.keyword, 1)
    ON CONFLICT DO
        UPDATE SET
            entry_counter=(
                IFF((SELECT entry_counter FROM HIS_COUNTER WHERE keyword == NEW.keyword) < 10, (SELECT entry_counter FROM HIS_COUNTER WHERE keyword == NEW.keyword) + 1, 1)
        );

END;

(2) By Gunter Hick (gunter_hick) on 2025-01-21 06:16:53 in reply to 1.1 [link] [source]

You are most probably missing a definition of the IFF() function. User defined functions are required to be created for each connection.

(3) By Aask (AAsk1902) on 2025-01-21 07:02:03 in reply to 1.1 [link] [source]

IFF(( ...

IIF (If and only IF) instead of IFF?

(4) By anonymous on 2025-01-21 07:04:06 in reply to 1.1 [link] [source]

Do you want IIF https://sqlite.org/lang_corefunc.html#iif.

Could you use excluded.entry_counter in your UPDATE instead of the SELECT, as in the examples here https://sqlite.org/lang_upsert.html?

(5) By 4xrasy (C17_4xrasy) on 2025-01-21 08:49:41 in reply to 2 [link] [source]

So I need to upgrade to version 3.48 if I want to use IIF. Or load the function with a setting in $HOME/.sqliterc

(6) By SeverKetor on 2025-01-21 08:58:20 in reply to 5 [link] [source]

No, you just need to spell the function name correctly. Use IIF (or alternatively in 3.48, IF) instead of IFF.

(7) By Richard Hipp (drh) on 2025-01-21 10:57:27 in reply to 6 [link] [source]

An historical note: The spelling of "IIF" comes from Microsoft SQL Server. I didn't make that up.

I am guessing that the reason that the function is spelled "IIF" in SQL Server is because "IF" is a keyword in the SQL language, and the parser used by SQL Server cannot handle a function name that is also a keyword. But SQLite's parser has no such limitation, and so as of version 3.48.0, SQLite allows you to spell the function name as "IF" as an alternative to the (somewhat awkward) "IIF".

(8) By Bo Lindbergh (_blgl_) on 2025-01-21 11:57:11 in reply to 7 [source]

How much work would it be to extend iif to accept an arbitrary number of arguments? That is,

    iif(x1, y1, x2, y2, z)

would be equivalent to

    case
        when x1 then y1
        when x2 then y2
        else z
    end

and so forth.

(9.2) By Aask (AAsk1902) on 2025-01-21 12:36:31 edited from 9.1 in reply to 8 [link] [source]

The syntax is IIF(condition, returniftrue, returniffalse)

So you can simply nest IIF e.g.

iif(field='x1', y1, iif(field='x2',y2,z))

I don't know how IIF implemented in SQLite. The ternary operator (namely, ? ) works in a similar way as IIF e.g.

contition ? trueValue : falsevalue

With the ternary operator, if the condition is false the truevalue (can be an expression) is simply skipped, if the condition is false, the truevalue (can be an expression) is skipped. If IIF does not skip based on the condition, nested IIF might lead to slow queries.

(10) By Richard Hipp (drh) on 2025-01-21 12:34:22 in reply to 8 [link] [source]

Not hard. See https://sqlite.org/src/info/f13ef591848e7c69. Most of the work will be in documentation and testing, and figuring out whether or not this is really a good idea.

(11) By Bo Lindbergh (_blgl_) on 2025-01-21 12:52:16 in reply to 9.2 [link] [source]

The flat version would be more readable than the nested version (to me, anyway).

The docs say:

(12) By Balaji Ramanathan (balaji) on 2025-01-21 16:10:32 in reply to 7 [link] [source]

IIF stands for "immediate if". It is a built-in function in both SQL server and MS Access.