SQLite Forum

Timeline
Login

19 forum posts by user kelvimchop

2021-10-15
14:15 Edit reply: (Deleted) (artifact: c99c0be361 user: kelvimchop)

Thank you ! Problem solved

14:15 Delete reply: (Deleted) (artifact: 07fe07d1ba user: kelvimchop)
Deleted
14:15 Edit: (Deleted) (artifact: f64c7d9306 user: kelvimchop)
Deleted
14:01 Reply: (Deleted) (artifact: 3db7f79919 user: kelvimchop)

Sorry, I think I haven't explained it clearly. I modify the post, if you may, please read the post again, thank you!

14:00 Edit: (Deleted) (artifact: 9d71af5399 user: kelvimchop)

Say

SELECT julianday('2021-01-02 01:00:00')-julianday('2021-01-01 04:00:00') 1 day and 3 hours will be 1.125 days

= 2459216.5416666665 - 2459215.6666666665 = 0.875 = julianday

How to transfer this 'julianday' back to days?

For example, 10 days and 6 hours will be 10.25 days

EDIT: Better explanation

12:33 Post: (Deleted) (artifact: befebdcdc9 user: kelvimchop)

Say

SELECT julianday('2021-01-02 01:00:00')-julianday('2021-01-01 04:00:00')

= 2459216.5416666665 - 2459215.6666666665 = 0.875

How to transfer this '0.875' back to hours?

For example, 10 days and 6 hours will be 10.25

11:16 Reply: Error Code : Error while executing SQL query on database ‘test’: no such column: price (artifact: 84d695303c user: kelvimchop)

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

10:07 Edit: Error Code : Error while executing SQL query on database ‘test’: no such column: price (artifact: 342a8b4556 user: kelvimchop)

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.

09:44 Edit: Error Code : Error while executing SQL query on database ‘test’: no such column: price (artifact: e7505a206c user: kelvimchop)

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

09:27 Edit reply: Error Code : Error while executing SQL query on database ‘test’: no such column: price (artifact: 5b11f32431 user: kelvimchop)

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

09:23 Edit reply: Error Code : Error while executing SQL query on database ‘test’: no such column: price (artifact: 16ee165304 user: kelvimchop)

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 suggest I to use julianday() to do it

And here is my edited version

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

Once again, thanks so much for your help

09:21 Reply: Error Code : Error while executing SQL query on database ‘test’: no such column: price (artifact: 71a6b390a2 user: kelvimchop)

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

Would you please explain the subselect? And,

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 suggest I to use julianday() to do it

And here is my edited version

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

Once again, thanks so much for your help

09:00 Edit reply: Error Code : Error while executing SQL query on database ‘test’: no such column: price (artifact: 36e15a1254 user: kelvimchop)

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

08:59 Reply: Error Code : Error while executing SQL query on database ‘test’: no such column: price (artifact: 66a3406a5b user: kelvimchop)

thank for your reply, I manage to change the subquery inside the update statement , ``` CREATE TRIGGER updater AFTER UPDATE ON gameRental FOR EACH ROW WHEN OLD.rental_cost IS NULL AND NEW.date_back IS NOT NULL BEGIN UPDATE gameRental SET rental_cost = 3 + ( SELECT price FROM gameRental JOIN GameLicense USING ( license_id ) JOIN GameTitle 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

08:57 Edit reply: Error Code : Error while executing SQL query on database ‘test’: no such column: price (artifact: adfc1a150a user: kelvimchop)

https://ibb.co/VMrBR1V

Sorry here is the table structure from sqlitestudio

08:57 Reply: Error Code : Error while executing SQL query on database ‘test’: no such column: price (artifact: e6e82dfe99 user: kelvimchop)

https://ibb.co/VMrBR1V

08:37 Post: Error Code : Error while executing SQL query on database ‘test’: no such column: price (artifact: 179631651f user: kelvimchop)

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.

2021-09-30
10:54 Reply: How to filter find duplicate and show row with same field using sqlite code? (artifact: 7ab95276a8 user: kelvimchop)
My apologies. The expected result is to list all the personal detail of everyone who shares the same name with someone –
09:56 Post: How to filter find duplicate and show row with same field using sqlite code? (artifact: d046a9704a user: kelvimchop)
Hello, I am learning the SQL code and don't know how to find the duplicate row and show it out.
Here is the table

person_no name birthday
01 John 01/01/2000
02 John 15/03/2000
03 Marry 21/06/2000
04 Peter 23/12/2000
05 Jerry 12/07/2000

The person_no is unique for everyone, but the name may be same, anyway to find the people with the same name but different person_no ?

Sorry if my question bothers you