Thanks for the question, Muhammad.

Asked: March 18, 2021 - 9:21 pm UTC

Last updated: March 26, 2021 - 4:45 pm UTC

Version: 19

Viewed 1000+ times

You Asked

Hi,

I am new to Apex and I would like to add a dynamic action to automatically update the old end_date column when new record inserted. The idea is, for the new record, whatever we put in start_date, same date will automatically assign to previous record end_date. For Example

Business_Name | Business_id | City_name | City_id | Start_date | End_Date
ABC 1000 NY 20 03-10-2021 12/31/9999

When user create another record for same business name but with different city, the end_date from previous record set to the start_date for new record.

Business_Name | Business_id | City_name | City_id | Start_date | End_Date
ABC 1000 NY 20 03-10-2021 03-15-2021 --Old Record
ABC 1000 Dalas 30 03-15-2021 12/31/9999 --New Record

IS there any way that i can achieve this without triggers?

and Chris said...

I would create a procedure that looks something like this:

create or replace procedure insert_rec ( 
  p_id int, latest_start date, ...
) as
  latest_end date;
begin

  update ...
  set    end_date = sysdate
  where  id = p_id
  and    start_date = latest_start
  returning end_date into latest_end;
  
  if sql%rowcount = 0 then
    raise_application_error ( 
      -20001,
      'Row start date mismatch'
    );
  end if;
  
  insert into ... ( id, start_date, ... 
    values ( p_id, latest_end, ... );
  
end insert_rec;
/

Then call this in your form submission process.

The start date check is to avoid the lost-update problem, where two people try and change the same record at the same time.

(sql%rowcount could be zero because you search for an invalid id; fixing this is an exercise for the reader ;)

Rating

(8 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Muhammad, March 23, 2021 - 4:00 pm UTC

Tom,

I did try with the procedure. Though it insert the new record but the old record still not updating. END_DATE is still the same even user insert a new record. Again, my expectation is when user insert a new record for the same id, the end_date from previous record replaced with new START_DATE.

March 23, 2021 - 4:29 pm UTC

Please show us an example of what you're tried - include all the code we need to run your example; create table, inserts, etc.

Muhammad, March 23, 2021 - 4:49 pm UTC

For example: I have an entry below

Business_Name | Business_id | City_name | City_id | Start_date | End_Date
ABC | 1000 | NY | 20| 03-10-2021 | 12/31/9999

When user inserts another entry for the same Business ID with different City name then END_DATE of previous record replaced with the START_DATE of new record.

Something like this:

Business_Name | Business_id | City_name | City_id | Start_date | End_Date
ABC | 1000 | NY | 20| 03-10-2021 | 03-23-2021 --Old entry
ABC | 1000 | CT | 20| 03-23-2021 | 12/31/9999 --New Entry

The procedure i have created is as follow:

create or replace procedure prc_update (p_Business_Name varchar2,
p_Business_id int,
p_City_name varchar2,
p_City_id varchar2,
p_Start_date date,
p_End_Date date)
AS
v_end_date date;
begin

update my_table
set end_date = sysdate
where Business_id = p_Business_id
returning end_date into v_end_date;

if sql%rowcount = 0 then
raise_application_error (
-20001,
'Row start date mismatch'
);
end if;

insert into my_table (Business_Name,Business_id,City_name,City_id,Start_date,End_Date)
values (p_Business_Name,p_Business_id,p_City_name,p_City_id,v_end_date,p_End_Date);

end prc_update;
/

March 24, 2021 - 9:11 am UTC

Looks fine to me...

create table my_table (
  business_name varchar2(100),
  business_id int,
  city_name varchar2(100),
  city_id int,
  start_date date,
  end_date date
);

insert into my_table (Business_Name,Business_id,City_name,City_id,Start_date,End_Date)
values ('test',1,'test',1,date'1999-12-31',date'9999-12-13');
commit;

create or replace procedure prc_update (p_Business_Name varchar2,
p_Business_id int,
p_City_name varchar2,
p_City_id varchar2,
p_Start_date date,
p_End_Date date)
AS
v_end_date date;
begin

update my_table
set end_date = sysdate
where Business_id = p_Business_id
returning end_date into v_end_date;

if sql%rowcount = 0 then
raise_application_error (
-20001,
'Row start date mismatch'
);
end if;

insert into my_table (Business_Name,Business_id,City_name,City_id,Start_date,End_Date)
values (p_Business_Name,p_Business_id,p_City_name,p_City_id,v_end_date,p_End_Date);

end prc_update;
/ 

exec prc_update ( 'test', 1, 'new city', 1, sysdate, date'9999-12-31' );

select * from my_table;

BUSINESS_NAME   BUSINESS_ID CITY_NAME   CITY_ID START_DATE             END_DATE               
test                      1 test              1 31-DEC-1999 00:00:00   24-MAR-2021 09:10:07    
test                      1 new city          1 24-MAR-2021 09:10:07   31-DEC-9999 00:00:00

What exactly happens when you call the code like this?

Muhammad, March 24, 2021 - 12:59 am UTC

Then, i create a process and there i provide the following plsql block:

BEGIN
insert_rec (:P11_BUSINESS_NAME,
:P11_BUSINESS_ID,
:P11_CITY_NAME,
:P11_CITY_ID,
:P11_START_DATE,
:P11_END_DATE);
END;

Under 'Server-Side Condition', When button press, I provide CREATE.

March 24, 2021 - 9:12 am UTC

This calls insert_rec; your previous code created prc_update.

So which are you using?

When button press, I provide CREATE.

I'm unsure what you mean by this. Please clarify.

Muhammad, March 25, 2021 - 2:33 pm UTC

It is working now. I put this process in a wrong place. When i put this under 'After Submit', then it is working. Thanks a lot for your help. Really appreciated!

March 25, 2021 - 6:11 pm UTC

You're welcome

Muhammad, March 25, 2021 - 2:41 pm UTC

One last question, for end_date, i wanted to display sysdate -1 and for the timing it hardcoded as 11:59:59.999999999 PM. How can I do that?

March 25, 2021 - 6:15 pm UTC

So you want to set it to just before midnight today?

You can use:

trunc ( sysdate ) - 1 + ( 86399 / 86400 )

For dates; use a smaller fraction if you have a timestamp with fractional seconds

In general it's much better to have end date prev row = start date of next; i.e. the same date.

This avoids any potential rounding issues.

Muhammad, March 25, 2021 - 6:42 pm UTC

I did try with the following approach and it is returning what I am exactly expecting.

select to_timestamp(to_char(sysdate-1,'dd-mm-yyyy')||'23:59:59.999999999' ,'dd-mm-yyyy hh24:mi:ss.FF') from dual;

Return

24-MAR-21 11.59.59.999999999 PM

But when i put this in my update statement, it will return the '25-MAR-21 12.00.00.000000000 AM'. I don't know why it is not working with update statement. Here is my update statement;

update ...
set end_date = to_timestamp(to_char(sysdate-1,'dd-mm-yyyy')||'23:59:59.999999999' ,'dd-mm-yyyy hh24:mi:ss.FF')
where id = p_id
and start_date = latest_start
returning end_date into latest_end;

Any idea why it is returning the wrong date?

March 26, 2021 - 7:01 am UTC

Is end_date a date or a timestamp ?

There is no fractions of a second in a date

Muhammad, March 26, 2021 - 12:19 pm UTC

timestamp

Muhammad, March 26, 2021 - 2:47 pm UTC

Also, I am getting an error when I try to insert a new record.

March 26, 2021 - 4:45 pm UTC

Please show us a complete example of what you're doing:

- create table
- sample data (in the form of inserts)
- the update you're running

More to Explore

SQL

The Oracle documentation contains a complete SQL reference.

Breadcrumb

Question and Answer

You Asked

and Chris said...

Rating

Comments

More to Explore

SQL

Automatically update the previous end_date column when new record inserted

Breadcrumb

Question and Answer

You Asked

and Chris said...

Rating

Comments

More to Explore

SQL