In DBeaver version 23.3.4.202402060628 I have the following SQLite3 code (for a high speed injection moulded part and package tracking machine in a factory):
--------------------------------------------------------------------------------
-- SQLite TABLE
DROP TABLE IF EXISTS pkgdata;
CREATE TABLE pkgdata (
DateTime TEXT, -- source format = "DD.MM.YYYY HH:mm:ss.sss"
Weight REAL,
Height REAL,
Boxes INT,
Parts INT,
Spread REAL GENERATED ALWAYS AS (Weight - Height) STORED,
gen_pkg_year INT GENERATED ALWAYS AS (SUBSTRING(DateTime, 7, 4)) STORED,
gen_pkg_month INT GENERATED ALWAYS AS (SUBSTRING(DateTime, 4, 2)) STORED,
gen_pkg_day INT GENERATED ALWAYS AS (SUBSTRING(DateTime, 1, 2)) STORED,
gen_pkg_wkday INT GENERATED ALWAYS AS (strftime('%w',DateTime)) STORED,
gen_pkg_per TEXT GENERATED ALWAYS AS (strftime('%p',DateTime)) STORED,
gen_pkg_hr INT GENERATED ALWAYS AS (SUBSTRING(DateTime, 12, 2)) STORED,
gen_pkg_min INT GENERATED ALWAYS AS (SUBSTRING(DateTime, 15, 2)) STORED,
gen_pkg_sec INT GENERATED ALWAYS AS (SUBSTRING(DateTime, 18, 2)) STORED,
gen_pkg_ms INT GENERATED ALWAYS AS (SUBSTRING(DateTime, 21, 3)) STORED,
gen_ISODate TEXT GENERATED ALWAYS AS (
printf('%04d-%02d-%02d %02d:%02d:%02d.%03d',
gen_pkg_year, gen_pkg_month, gen_pkg_day,
gen_pkg_hr, gen_pkg_min, gen_pkg_sec, gen_pkg_ms)
) STORED,
prev_Weight REAL,
prev_Height REAL,
Weight_diff REAL,
Height_diff REAL,
Weight_move TEXT,
Height_move TEXT,
gap TEXT
);
--------------------------------------------------------------------------------
-- SQLite INDICES - created one line at a time
CREATE INDEX pkg_idx_ymd ON pkgdata (gen_pkg_year, gen_pkg_month, gen_pkg_day);
CREATE INDEX pkg_idx_mon ON pkgdata (gen_pkg_month);
CREATE INDEX pkg_idx_dom ON pkgdata (gen_pkg_day);
CREATE INDEX pkg_idx_dow ON pkgdata (gen_pkg_wkday);
CREATE INDEX pkg_idx_per ON pkgdata (gen_pkg_per);
CREATE INDEX pkg_idx_hr ON pkgdata (gen_pkg_hr);
CREATE INDEX pkg_idx_min ON pkgdata (gen_pkg_min);
--------------------------------------------------------------------------------
-- SQLite TRIGGER
DROP TRIGGER IF EXISTS pkg_tr_fill_calculated_columns;
CREATE TRIGGER pkg_tr_fill_calculated_columns
AFTER INSERT ON pkgdata
BEGIN
UPDATE pkgdata
SET prev_Weight = previous.Weight,
prev_Height = previous.Height,
Weight_diff = NEW.Weight - previous.Weight,
Height_diff = NEW.Height - previous.Height,
Weight_move = CASE
WHEN Weight > prev_Weight THEN 'H'
WHEN Weight = prev_Weight THEN 'S'
WHEN Weight < prev_Weight THEN 'L'
END,
Height_move = CASE
WHEN Height > prev_Height THEN 'H'
WHEN Height = prev_Height THEN 'S'
WHEN Height < prev_Height THEN 'L'
END,
gap = CASE
WHEN Height > prev_Weight THEN 'U'
WHEN Weight < prev_Height THEN 'D'
ELSE ''
END
FROM (SELECT * FROM pkgdata
ORDER BY gen_ISODate DESC
LIMIT 1, 1) AS previous -- get the previous row
WHERE pkgdata.DateTime = NEW.DateTime;
END;
--------------------------------------------------------------------------------
-- SQLite INSERT DATA
INSERT INTO pkgdata (
DateTime, -- TEXT
Weight, -- REAL
Height, -- REAL
Boxes, -- INT
Parts) -- INT
VALUES
('01.01.2010 00:00:00.817',0.89755,0.89725,0,0),
('01.01.2010 00:00:01.157',0.89753,0.89728,0,0),
('01.01.2010 00:00:01.457',0.89755,0.89725,0,0),
('01.01.2010 00:00:01.737',0.89755,0.89725,0,0),
('01.01.2010 00:00:02.417',0.89755,0.89725,0,0),
('01.01.2010 00:00:02.747',0.8975,0.8973,0,0),
('01.01.2010 00:00:06.295',0.89755,0.89725,0,0),
('01.01.2010 00:00:06.566',0.89758,0.89723,0,0),
('01.01.2010 00:00:06.866',0.89755,0.89725,0,0),
('01.01.2010 00:00:09.985',0.89755,0.89725,0,0),
('01.01.2010 00:00:10.325',0.89755,0.89725,0,0),
('01.01.2010 00:00:11.055',0.89755,0.89725,0,0),
('01.01.2010 00:00:12.385',0.89755,0.89725,0,0),
('01.01.2010 00:00:12.604',0.8975,0.8973,0,0),
('01.01.2010 00:00:12.884',0.89755,0.89725,0,0),
('01.01.2010 00:00:13.114',0.89758,0.89723,0,0),
('01.01.2010 00:00:15.283',0.89758,0.89723,0,0),
('01.01.2010 00:00:15.514',0.8975,0.8973,0,0),
('01.01.2010 00:00:15.684',0.8975,0.8973,0,0),
('01.01.2010 00:00:17.083',0.8975,0.8973,0,0),
('01.01.2010 00:00:17.693',0.8975,0.8973,0,0)
returning *;
I had working code but now I am getting an error when I get to the INSERT statement (DBeaver accepts each block):
SQL Error 1: [SQLITE_ERROR] SQL error or missing database (ambiguous column name: Weight)
There is only one table with one column named Weight. From this post I thought there is something still in memory, so I disconnected all other databases and deleted all tables from this one and started from scratch. I tried using fully qualified names by prepending table name:
INSERT INTO pkgdata (
pkgdata.DateTime, -- TEXT
pkgdata.Weight, -- REAL
pkgdata.Height, -- REAL
pkgdata.Boxes, -- INT
pkgdata.Parts) -- INT
VALUES
...
But then it throws a syntax error on the period.
"… but now I am getting an error when I get to the
INSERTstatement …"
AFTER INSERT trigger in its UPDATE event fails to distinguish between inserted (before trigger event) and updated (after trigger event) associated row on all column names of every WHEN clause in each of three CASE expressions, hence the "ambiguous column name" error.
"DBeaver accepts each block"
It doesn't result in syntax error when issuing CREATE TRIGGER because the CASE expression itself is valid (only in the context of a trigger event it becomes ambiguous).
"I tried using fully qualified names by prepending table name: … But then it throws a syntax error on the period."
Prepend column names with intended before- or after trigger event reference (NEW.column-name or OLD.column-name) on every WHEN clause in each of the trigger UPDATE event's CASE expressions. Also, consider using virtual columns (or the calculations that would generate them) in the CASE expressions' WHEN clauses rather than separately SET that data in the same trigger UPDATE event which calculations depend on it (redundantly duplicating in the process).
Corrected TRIGGER statement:
CREATE TRIGGER pkg_tr_fill_calculated_columns
AFTER INSERT ON pkgdata
BEGIN
UPDATE pkgdata
SET prev_Weight = previous.Weight,
prev_Height = previous.Height,
Weight_diff = NEW.Weight - previous.Weight,
Height_diff = NEW.Height - previous.Height,
Weight_move = CASE
WHEN NEW.Weight > previous.Weight THEN 'H'
WHEN NEW.Weight = previous.Weight THEN 'S'
WHEN NEW.Weight < previous.Weight THEN 'L'
END,
Height_move = CASE
WHEN NEW.Height > previous.Height THEN 'H'
WHEN NEW.Height = previous.Height THEN 'S'
WHEN NEW.Height < previous.Height THEN 'L'
END,
gap = CASE
WHEN NEW.Height > previous.Weight THEN 'U'
WHEN NEW.Weight < previous.Height THEN 'D'
ELSE ''
END
FROM (SELECT * FROM pkgdata
ORDER BY gen_ISODate DESC
LIMIT 1, 1) AS previous -- get the previous row
WHERE pkgdata.DateTime = NEW.DateTime;
END;
Corrected dbfiddle.