Retirement Date Function in Oracle SQL

Question

Please I want to calculate the retirement of an employee from his Date of First Appointment using a function before retrieving the result using a trigger and store it in the Retirement date column: but I am not good with the syntax please can someone help:... Below is my code for the function

CREATE OR REPLACE Function EDOR_DATE
(DOFA IN date)
RETURN date
IS
NEW_EDOR_RESULT date;

BEGIN

SELECT DOFA + ((365*35) + 9) as NEW_EDOR
FROM EMPLOYEES_MASTER_DETAILS; 

fetch NEW_EDOR into NEW_EDOR_RESULT;

RETURN  NEW_EDOR_RESULT;

END;

This is my original code

Auwal
– Auwal

2017-04-03 14:28:13 +00:00
Commented Apr 3, 2017 at 14:28 — Auwal
– Auwal, Commented Apr 3, 2017 at 14:28

Belayer · Accepted Answer · 2017-04-03 18:05:47Z

First a couple comments on the changes you indicated you made to the function:

Removing the "IN" from the declaration actually accomplishes nothing. If you do not specify "IN', "OUT", or "IN OUT" the compiler defaults to "IN". All removing it does is a change from an explicit to implicit declaration.
Placing a semi-colon (;) where indicated will generate an error.

As for the function itself you are making it way more complicated than necessary. A simple assignment is all that's needed. Also, Boneist's suggestion of add_months is the correct function for adding years as it will adjust for leap year and number of days per month (if needed).

Thus your function reduces to:

create or replace function edor_date(dofa in date) 
return date 
is
    l_edor_date date; 
begin
    l_edor_date := add_months(dofa, 35*12) ;
    return  l_edor_date ;
end;

or even further to just:

create or replace function edor_date (dofa in date) 
return date 
is
begin
    return  add_months(dofa, 35*12) ;
end;

BTW: I actually like the idea of using a function for this as it hides the implementation details of the business rule. However, it does impose a slight overhead for each call.

fleetmack · Accepted Answer · 2017-04-06 20:53:57Z

0

CREATE OR REPLACE Function EDOR_DATE
(DOFA date) 
RETURN date 
IS
NEW_EDOR_RESULT date;

BEGIN
--I am converting this to a case statement that is easier to read
select 
case 
when dofa - dob <= 25 then dofa + ((365*35)+9) 
else dob + ((365*60)+9)
end
into new_edor_result
from employees_master_details;

return new_edor_result;
end;

edited Apr 6, 2017 at 20:53

answered Apr 3, 2017 at 14:27

fleetmack

3881 gold badge4 silver badges12 bronze badges

Comments

Boneist · Accepted Answer · 2017-04-03 14:32:31Z

0

This can be done without needing to use a function. Also, why are you adding numbers of days? A year is not 365 days long. You should be using the add_months() feature.

Here is how I would do this, assuming all the columns in question belong to the EMPLOYEES_MASTER_DETAILS table:

select emp_id,
       dob,
       hire_date,
       case when months_between(hire_date, dob) <= 25 * 12 then add_months(hire_date, 35*12)
            else add_months(dob, 60*12)
       end retirement_date
from   EMPLOYEES_MASTER_DETAILS emd;

If you need to calculate the retirement upon insert, then simply use the case expression as part of the insert statement.

answered Apr 3, 2017 at 14:32

Boneist

23.6k1 gold badge28 silver badges42 bronze badges

Collectives™ on Stack Overflow

Retirement Date Function in Oracle SQL

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related