Automatically Populte Incremented Day-CodePudding

In my table I have 3 dates that are important:

   Scrap_Date
   Due_Date
   Follow_Up_Date

The Scrap Date is entered into a form. I would like to have the form to where once the "Scrap_Date" is entered, "Due_Date" would automatically populate a date 7 days later, and "Follow_Up_Date" would automatically populate t a date 14 days later.

I still want these dates to display on the form, but for them to automatically populate to these dates without being able to be changed.

What is the best way to go about this?

I've tried setting up a Select List, but struggled with what to code with SQL.

I have also tried this to no success:

    select SCRAP_DATE,
          DUE_DATE,
          FOLLOW_UP_DATE,
    from SCRAP_BODY_SYSTEM
    where DUE_DATE = SCRAP_DATE   7
    AND FOLLOW_UP_DATE = SCRAP_DATE   14

I appreciate any help, thank you!

CodePudding user response：

You shouldn't be doing it that way. If due_date and follow_up_date are always 7/14 days after scrap_date, then what's their purpose? You can always calculate them when you need them.

If you still want to have them in the table, make them virtual (see lines #5 and 6); doing so, Oracle will do everything for you.

SQL> create table test
  2  (id               number   generated always as identity,
  3   scrap_date       date,
  4   --
  5   due_date         date     as (scrap_date   7),
  6   follow_up_date   date     as (scrap_date   14),
  7   --
  8   constraint pk_test primary key (id)
  9  );

Table created.

(just to know what is what; you don't have to do this):

SQL> alter session set nls_date_format = 'dd.mm.yyyy';

Session altered.

In your Apex form, you'd insert only scrap_date:

SQL> insert into test (scrap_date) values (date '2022-06-15');

1 row created.

Table contents is then:

SQL> select * from test;

        ID SCRAP_DATE DUE_DATE   FOLLOW_UP_
---------- ---------- ---------- ----------
         1 15.06.2022 22.06.2022 29.06.2022

SQL>

See? Everything is already set.

CodePudding user response：

Not sure about APEX GUI issues but two points:

you seem to select rows which already satisfy your "T, T 7, T 14" condition. You might rather want to somehow derive them, for instance compute in select clause?
the syntax for date addition is

select SCRAP_DATE, SCRAP_DATE   interval '7' day, SCRAP_DATE   interval '14' day 
from SCRAP_BODY_SYSTEM