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.

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;

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

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
;