CODEWITHSUNDEEP
sqloracle-databaseplsql
Auwal
Auwal

Reputation: 17

Retirement Date Function in Oracle SQL

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;

Upvotes: 0

Views: 1930

Answers (3)

fleetmack
fleetmack

Reputation: 378

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;

Upvotes: 0

Belayer
Belayer

Reputation: 14886

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

  1. 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.
  2. 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.

Upvotes: 1

Boneist
Boneist

Reputation: 23588

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.

Upvotes: 0

Related Questions