sqlite generated column dependent expression

Question

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 

     

question from:https://stackoverflow.com/questions/65898026/sqlite-generated-column-dependent-expression

Answer 1

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
);