SQLite Forum

Error Code : Error while executing SQL query on database ‘test’: no such column: price
Login

Error Code : Error while executing SQL query on database ‘test’: no such column: price

(1.2) By monkelvin (kelvimchop) on 2021-10-15 10:07:30 edited from 1.1 [link] [source]

SQLite question (Sorry if I choose the wrong topic category ) I am creating a trigger for my SQLite code, which it uses to update the field rental_cost (inside table Rental, default NULL) if someone UPDATE or INSERT the related table, the rental_cost is a REAL field that requires calculation and data from another table so I use JOIN to grip data from other tables, however, it shows the above error code when I tried to test it.

Rental table structure image|549x160

The Trigger

CREATE TRIGGER updater
         AFTER UPDATE
            ON Rental
      FOR EACH ROW
          WHEN OLD.rental_cost = NULL AND 
               NEW.date_back != NULL
BEGIN
    SELECT price
      FROM Rental
           JOIN
           License USING (
               license_id
           )
           JOIN
           Title USING (
               title
           );
    UPDATE Rental
       SET rental_cost = 3 + price * 0.05 * (date_out) - (date_back) 
     WHERE rental_cost = NULL;
END;
Test code to update the table
UPDATE Rental
   SET date_back = '0000-00-11 11:11:11'
 WHERE gamer_id = '12' AND 
       license_id = '71811';

Expected outcome: the data_back update manually, and trigger detect the update, calculate and update the rental_cost automatically.

Edited 1.0 : The SQLiteStudio empty field IS NOT NULL, when I use ` SELECT rental_cost FROM gameRental WHERE rental_cost IS NOT NULL the last field which is empty by default, show up in the query result. Vice versa, when IS NULL, the query result show nothing

Edited 2.0 : This is weird. When I completely delete the table and recreate it, use SQL edit instead of the provided Triggers Function from the SQLITeStudio Tools bar, and type all the code myself, the trigger works.

(2) By Stephan Beal (stephan) on 2021-10-15 08:51:02 in reply to 1.0 [link] [source]

          WHEN OLD.rental_cost = NULL AND 
               NEW.date_back != NULL

NULL will never compare properly that way. Use IS NULL and IS NOT NULL to compare against NULL values.

(5.1) By monkelvin (kelvimchop) on 2021-10-15 09:00:06 edited from 5.0 in reply to 2 [link] [source]

thank for your reply, I manage to change the subquery inside the update statement ,

CREATE TRIGGER updater
         AFTER UPDATE
            ON Rental
      FOR EACH ROW
          WHEN OLD.rental_cost IS NULL AND 
               NEW.date_back IS NOT NULL
BEGIN
    UPDATE Rental
       SET rental_cost = 3 + (
                                 SELECT price
                                   FROM Rental
                                        JOIN
                                        License USING (
                                            license_id
                                        )
                                        JOIN
                                        Title USING (
                                            title
                                        )
                             )
*                            0.05 * (date_out) - (date_back) 
     WHERE rental_cost = NULL;
END;

however, the new problem is the trigger not detecting the UPDATE statement and not fire. I believe is something wrong with my WHEN statement

(3) By Tim Streater (Clothears) on 2021-10-15 08:51:53 in reply to 1.0 [source]

The table structure image is not visible.

(4.1) By monkelvin (kelvimchop) on 2021-10-15 08:57:20 edited from 4.0 in reply to 1.0 [link] [source]

https://ibb.co/VMrBR1V

Sorry here is the table structure from sqlitestudio

(6.1) By Gunter Hick (gunter_hick) on 2021-10-15 09:04:06 edited from 6.0 in reply to 1.0 [link] [source]

Lots of misconceptions here.

x) SQLite trigger programs do not have variables. The result column price from the SELECT ist not available to the separate UPDATE statement. Use a subselect to retrieve the price that relates to the row being updated.

x) The UPDATE is going to affect every row of Rental that matches the WHERE clause, not just the NEW row. Use rowid = NEW.rowid instead

x) rental_cost = NULL is always FALSE; nothing is ever equal to NULL, not even NULL. Use IS NULL to check for NULL values.

x) I think you meant to compute (date_out - date_back), not subtract a date from a product of a price and a date.

Edit: Subtracting dates stored as TEXT will probably not yield the expected result.

(7.2) By monkelvin (kelvimchop) on 2021-10-15 09:27:37 edited from 7.1 in reply to 6.1 [link] [source]

Thanks so much for your clarification, it means a lot.

Would you please explain the subselect? And,

The date_back and rental_cost is default to be NULL, date_back will change until someone update new datetime string,and the rental_cost will be update by the trigger because of someone UPDATE the table.

The calculation of the rental cost is using the following formula

rental cost = 3 + price × 0.05 × days rented

which the days rented is done by subtraction of two rows date_out - date_back date_out & date_back is two TEXT datatype with YYYY-MM-DD HH:MM: SS string format

My friend suggested I use julianday() to do it

And here is my edited version

CREATE TRIGGER updater AFTER UPDATE ON Rental FOR EACH ROW WHEN OLD.rental_cost IS NULL AND NEW.date_back IS NOT NULL BEGIN UPDATE Rental SET rental_cost = 3 + ( SELECT price FROM Rental JOIN License USING ( license_id ) JOIN Title USING ( title ) ) * 0.05 * (date_out) - (date_back) WHERE rental_cost IS NULL; END;

Once again, thanks so much for your help

(8) By Gunter Hick (gunter_hick) on 2021-10-15 10:24:58 in reply to 7.2 [link] [source]

This will probably calculate the number of days: (julianday(date_out) - julianday(date_back) + 1)

You need to use
 WHERE rowid = NEW.ROWID
to restrict changes to the affected row. 

 WHERE rental_cost IS NULL
will update ALL of the rows that have a NULL rental cost

The price in the subselect will be the one from the first Rental record visited in the subselect. I don't think that is what you intend. If you want the price from the affected row, just use NEW.price instead.

(9) By monkelvin (kelvimchop) on 2021-10-15 11:16:49 in reply to 8 [link] [source]

Thank you so much, that's very clear! Wish you a nice day!