jeudi 26 août 2021

Add new column with Boolean in PL/SQL

I'm learning PL/SQL right now and I have a doubt. I have created the following table called tbProducts:

CREATE TABLE tbProducts (
                        nIDProduct NUMBER(2) NOT NULL,
                        vDescription VARCHAR2(20 CHAR),
                        nQuantity NUMBER(3),
                        nPrice NUMBER(6,2),
                        dLastDate DATE)

And I have inserted some values so the table is like this:

nIDProduct | vDescription | nQuantity | nPrice | dLastDate
         1 |     'Hammer' |        50 |   3.25 | 13-MAY-2021
         2 |      'Nails' |       100 |   0.75 | 28-AUG-2021
         3 |     'Screws' |       250 |   0.16 | 21-JUL-2021

Now what I'm looking for is a boolean variable that can be called bUpdate that returns FALSE if today's date (26-AUG-2021) is greater than dLastDate and returns TRUE if it's less or equal so the table would look like this:

nIDProduct | vDescription | nQuantity | nPrice | dLastDate   | bUpdate
         1 |     'Hammer' |        50 |   3.25 | 13-MAY-2021 | FALSE
         2 |      'Nails' |       100 |   0.75 | 28-AUG-2021 | TRUE
         3 |     'Screws' |       250 |   0.16 | 21-JUL-2021 | FALSE

I am trying doing the following:

DECLARE
     bUpdate BOOLEAN;

BEGIN
     
     SELECT t.*, bUpdate(
                        IF SYSDATE > dLastDate THEN
                               bUpdate := FALSE;
                        ELSE
                               bUpdate := TRUE;
                        END IF
         FROM tbProducts t
         ;
END;

I get an error saying that a FROM was expected after the SELECT statement. Since I'm still learning I don't know what it's wrong in this statement, could someone help me? Is there a way to do it with a CURSOR too? Thank you all!

Aucun commentaire:

Enregistrer un commentaire