Hi Everyone,

I'm having trouble using CASE in place of the missing IF. I am trying to perform an INSERT if a record is missing.

According to Language Expressions, Section 7, https://www.sqlite.org/lang_expr.html:

The only difference between the following two CASE expressions is that the x expression is evaluated exactly once in the first example but might be evaluated multiple times in the second:

CASE x WHEN w1 THEN r1 WHEN w2 THEN r2 ELSE r3 END

CASE WHEN x=w1 THEN r1 WHEN x=w2 THEN r2 ELSE r3 END

The built-in iif(x,y,z) SQL function is logically equivalent to...

I can't use IFF because my script may run on older clients.

I'm getting a parse error when I place the following in my *.sql source file:

CREATE TABLE IF NOT EXISTS blacklist_lcl
(
	bl_id INTEGER PRIMARY KEY AUTOINCREMENT,
	bl_name VARCHAR,
	bl_number INTEGER,
	bl_date DATETIME
);
...

CASE
    WHEN
        SELECT COUNT(*) FROM blacklist_lcl WHERE bl_number="4105551212" == 0
    THEN
        INSERT INTO blacklist_lcl (bl_name, bl_number, bl_date) VALUES ("Spam User Test", "4105551212", datetime('now'))
END

It results in "SQL error: near "CASE": syntax error". Changing from '==' to '=' results in the same error.

My question is, how do I fix this?

CASE WHEN SELECT

You need a scalar value there. Try wrapping that SELECT in (...).

Edit: then see Gunter's explanation for why the part after that can't work.

As noted previously, you can't do that. However, you can instead insert using select instead of VALUES, and then include a where clause that checks for the value already existing. Or add a unique index and do INSERT OR IGNORE INTO etc. if that would work for whatever you're doing

Move the conditions to decide to execute the trigger. Like this for example:

CREATE TRIGGER IF NOT EXISTS INSERT_NEW_MONSTERS BEFORE INSERT ON monsters_rioolwater WHEN (select count(*) from monsters_rioolwater WHERE Date_of_measurement == NEW.Date_of_measurement AND RWZI_code == NEW.RWZI_code) == 1

BEGIN

INSERT OR REPLACE INTO rioolwater_monsters_archive (RM_Date_of_report, RM_Date_measurement, RM_RWZI_AWZI_code, OLD_RNA_flow_per_100000, NEW_RNA_flow_per_100000, RM_Reason)
VALUES (NEW.Date_of_report, NEW.Date_of_measurement, NEW.RWZI_code,
(SELECT RNA_flow_per_100000 FROM monsters_rioolwater WHERE RWZI_CODE == NEW.RWZI_code AND Date_of_measurement == NEW.Date_of_measurement), NEW.RNA_flow_per_100000, 'Aanpassing meting');

UPDATE OR ROLLBACK monsters_rioolwater
SET RNA_flow_per_100000 = NEW.RNA_flow_per_100000,
    Date_of_report = NEW.Date_of_report,
    DTS_UPDATED = datetime('now')
where RWZI_code == NEW.RWZI_code
  and Date_of_measurement == NEW.Date_of_measurement;

END;

Here is how I would do it:

INSERT INTO blacklist_lcl(
    bl_name,
    bl_number,
    bl_date
  )
SELECT
    "Spam User Test",
    "4105551212",
    DATETIME ('now')
FROM
    (SELECT 1)
LEFT JOIN
    blacklist_lcl
ON
    bl_number = 4105551212 -- Should not use quotes for integer literals
WHERE
    blacklist_lcl.rowid IS NULL
;