CODEWITHSUNDEEP
sqloracle-databasedatetime
Shaun Lin
Shaun Lin

Reputation: 3

Learning Oracle, Setting boolean based on date

I've just started a module in school on Oracle, I would like to ask for some help as I'm relatively new in regards to the capabilities of database systems in general.

For a part of one of my assignments, I'm trying to create a table where it stores customers details, there are columns of "LastBillPaidDate" as a DATE datatype and "hasUnpaidBill" and a BOOLEAN datatype.

When it comes to setting constraints, is there a way for me to make it such that when LastBillPaidDate is X days ago, I set hasUnpaidBill from false to true?

Thanks again and looking forward to learning from you all!

Upvotes: 0

Views: 113

Answers (1)

Littlefoot
Littlefoot

Reputation: 143053

Should you have the has_unpaid_bill column in the table at the first place? It depends on SYSDATE. The requirement smells like a virtual column, but - you can't use SYSDATE there as it is not a deterministic function. Therefore, how about a view instead? Keep data you need in the table, but query the view:

Table and several sample records:

SQL> create table test
  2    (id                  number,
  3     last_bill_paid_date date
  4    );

Table created.

SQL> insert into test values (1, date '2020-11-09');

1 row created.

SQL> insert into test values (1, date '2020-11-02');

1 row created.

View:

SQL> create or replace view v_test as
  2    select id,
  3           last_bill_paid_date,
  4           case when trunc(sysdate) - last_bill_paid_date > 5 then 'Y'
  5                else 'N'
  6           end has_unpai_dbill
  7    from test;

View created.

SQL>
SQL> select * from v_test;

        ID LAST_BILL_ H
---------- ---------- -
         1 09.11.2020 N
         1 02.11.2020 Y

SQL>

Upvotes: 1

Related Questions