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!

Answer

What you try wouldn’t work. If you want an additional column, you need to add the column to the table with an ALTER TABLE command – but in this case you’re adding a non-deterministic expression and that cannot be added as a virtual column.

The easiest way to achieve what you want is to create a view on top of the table with the case statement as illustrated below:

CREATE TABLE tbproducts (
                        nidproduct NUMBER(2) NOT NULL,
                        vdescription VARCHAR2(20 CHAR),
                        nquantity NUMBER(3),
                        nprice NUMBER(6,2),
                        dlastdate DATE);

INSERT INTO tbproducts (nidproduct,vdescription,nquantity,nprice,dlastdate) VALUES (1,'Hammer', 50,3.25,TO_DATE('13-MAY-2021','DD-MON-YYYY'));
INSERT INTO tbproducts (nidproduct,vdescription,nquantity,nprice,dlastdate) VALUES (2, 'Nails',100,0.75,TO_DATE('28-AUG-2021','DD-MON-YYYY'));
INSERT INTO tbproducts (nidproduct,vdescription,nquantity,nprice,dlastdate) VALUES (3,'Screws',250,0.16,TO_DATE('21-JUL-2021','DD-MON-YYYY'));  

CREATE VIEW tbproducts_v
AS
SELECT 
  nidproduct
 ,vdescription
 ,nquantity
 ,nprice
 ,dlastdate
 ,CASE WHEN SYSDATE > dlastdate THEN 'TRUE' ELSE 'FALSE' END as status
FROM  tbproducts;

select * from tbproducts_v;

NIDPRODUCT VDESCRIPTION          NQUANTITY     NPRICE DLASTDATE   STATU
---------- -------------------- ---------- ---------- ----------- -----
         1 Hammer                       50       3.25 13-MAY-2021 TRUE 
         2 Nails                       100        .75 28-AUG-2021 FALSE
         3 Screws                      250        .16 21-JUL-2021 TRUE 

If you insist on adding a new column then this is what you’d do.

ALTER TABLE tbproducts ADD status VARCHAR2(100);

UPDATE tbproducts t 
   SET (t.status) = 
      (SELECT 
        CASE WHEN SYSDATE > dlastdate THEN 'TRUE' ELSE 'FALSE' END 
         FROM tbproducts st 
        WHERE st.nidproduct = t.nidproduct);

if you just want to display the true/false in the console with a pl/sql block then this is an option (using implicit cursor for loop):

set serveroutput on size 999999
clear screen
DECLARE
BEGIN
  FOR r IN (SELECT * FROM tbproducts) LOOP
    dbms_output.put_line('Product '||r.vdescription ||', status: '||CASE WHEN SYSDATE > r.dlastdate THEN 'TRUE' ELSE 'FALSE' END);
    IF r.dlastdate < SYSDATE THEN
      dbms_output.put_line('Product '||r.vdescription ||' is expired !');
    END IF;
  END LOOP;

END;
/

note 1 : pl/sql has a boolean datatype, but sql does NOT have one. So in your table you’ll need to store a string (1/0, T/F, Y/N, TRUE/FALSE).

note 2 : it’s not a good practice to use camelcase in table names or column names. If you’re creating them without quotes the names are case insensitive anyway.