I have encountered this situation where i plan to insert a generated column which the expression (the expression to design quoted in (???..I need this suggestion expression too..???) schema table below. the output is a calculation depend on the value of broker_id and place to generated column name brokage.
Your sharing and guidance on the scenario is much appreciated.
Thank you and warm regards
new sqlite learner.
sqlite> .schema buy
CREATE TABLE buy(
buy_id INTEGER,
stock_id INTEGER,
investor_id INTEGER,
broker_id INTEGER,
unit INTEGER,
price REAL,
date TEXT,
cost REAL GENERATED ALWAYS AS (unit*price),
brokage REAL GENERATED ALWAYS AS (??? depend on broker_id for expression ???),
PRIMARY KEY (buy_id, stock_id, investor_id, broker_id),
FOREIGN KEY (stock_id) REFERENCES stock(stock_id) ON DELETE CASCADE ON UPDATE NO ACTION,
FOREIGN KEY (investor_id) REFERENCES stock(investor_id) ON DELETE CASCADE ON UPDATE NO ACTION
FOREIGN KEY (broker_id) REFERENCES stock(broker_id) ON DELETE CASCADE ON UPDATE NO ACTION
);
sqlite> .mode column
sqlite> .header on
sqlite> SELECT * FROM buy;
buy_id stock_id investor_id broker_id unit price date cost brokage
------ -------- ----------- --------- ----- ----- ---------- ------ --------
1 7 1 1 2000 0.68 2020-06-24 1360.0
2 25 1 3 2000 0.88 2020-10-22 1760.0
You can do it with a CASE expression:
CREATE TABLE buy(
buy_id INTEGER,
stock_id INTEGER,
investor_id INTEGER,
broker_id INTEGER,
unit INTEGER,
price REAL,
date TEXT,
cost REAL GENERATED ALWAYS AS (unit*price),
brokage REAL GENERATED ALWAYS AS (CASE broker_id WHEN 1 THEN 9 WHEN 3 THEN 28 END),
PRIMARY KEY (buy_id, stock_id, investor_id, broker_id),
FOREIGN KEY (stock_id) REFERENCES stock(stock_id) ON DELETE CASCADE ON UPDATE NO ACTION,
FOREIGN KEY (investor_id) REFERENCES stock(investor_id) ON DELETE CASCADE ON UPDATE NO ACTION
FOREIGN KEY (broker_id) REFERENCES stock(broker_id) ON DELETE CASCADE ON UPDATE NO ACTION
);