Let me start by saying, I've never written a function before, and I don't have access to create a function in my database (i.e. I can't test this function). I'm trying to come up with a function that I can ask my IT department to add for me. I'm hoping someone can take a look at what I've written and tell me if it should work or not, and if this is the right way to go about solving my problem.
I am trying to create a function to do a very simple workday calculation (adding/subtracting a particular number of workdays from a calendar date).
The database I'm working with has a table with the workday calendar in it. Here is a sample table and sample data, representative of what's in my workday calendar table:
CREATE TABLE caln
( clndr_dt DATE,
shop_days NUMBER(5)
CONSTRAINT caln_pk PRIMARY KEY (clndr_dt)
);
INSERT INTO caln
VALUES (To_Date('01/01/1980','mm/dd/yyyy'),0);
INSERT INTO caln
VALUES (To_Date('01/02/1980','mm/dd/yyyy'),1);
INSERT INTO caln
VALUES (To_Date('01/03/1980','mm/dd/yyyy'),2);
INSERT INTO caln
VALUES (To_Date('01/04/1980','mm/dd/yyyy'),3);
INSERT INTO caln
VALUES (To_Date('01/05/1980','mm/dd/yyyy'),3);
INSERT INTO caln
VALUES (To_Date('01/06/1980','mm/dd/yyyy'),3);
INSERT INTO caln
VALUES (To_Date('01/07/1980','mm/dd/yyyy'),4);
INSERT INTO caln
VALUES (To_Date('01/08/1980','mm/dd/yyyy'),5);
INSERT INTO caln
VALUES (To_Date('01/09/1980','mm/dd/yyyy'),6);
INSERT INTO caln
VALUES (To_Date('01/10/1980','mm/dd/yyyy'),7);
INSERT INTO caln
VALUES (To_Date('01/11/1980','mm/dd/yyyy'),8);
INSERT INTO caln
VALUES (To_Date('01/12/1980','mm/dd/yyyy'),8);
INSERT INTO caln
VALUES (To_Date('01/13/1980','mm/dd/yyyy'),8);
INSERT INTO caln
VALUES (To_Date('01/14/1980','mm/dd/yyyy'),9);
The actual table includes from 1/1/1980 though 12/31/2015.
I've written (and validated) this parameter query which does my workday (mday) calculation:
SELECT cal.clndr_dt
FROM CALN cal
, (
SELECT cal.shop_days+:mdays AS new_shop_days
FROM CALN cal
WHERE cal.clndr_dt =:start_date
) a
WHERE cal.shop_days = a.new_shop_days
AND ROWNUM =1
ORDER BY cal.clndr_dt;
Based on this query, I've created the following function (and I have no clue if it works or if the syntax is right, etc.):
CREATE OR REPLACE FUNCTION add_mdays
(start_date IN DATE,
mdays IN NUMBER(5))
RETURN DATE
IS
new_date DATE;
BEGIN
SELECT cal.clndr_dt
FROM CALN cal
, (
SELECT cal.shop_days+mdays AS new_shop_days
FROM CALN cal
WHERE cal.clndr_dt =start_date
) a
WHERE cal.shop_days = a.new_shop_days
AND ROWNUM =1
ORDER BY cal.clndr_dt;
RETURN new_date;
END add_mdays; //edit 9:31 AM - noticed I left off this bit
I'm also not sure how to have the function handle results that would return a date outside of the date range that is in the table (Before 1/1/1980 or after 12/31/2015--or, another way to look at it is, before the MIN value of caln.clndr_dt or after the MAX value of caln.clndr_dt).
My goal is to be able to use the function in a situation like the following:
First, here's a sample table and data:
CREATE TABLE orders
( ord_no NUMBER(5),
plan_start_dt DATE,
CONSTRAINT orders_pk PRIMARY KEY (ord_no)
);
INSERT INTO orders
VALUES (1,To_Date('01/08/1980','mm/dd/yyyy'));
INSERT INTO orders
VALUES (2,To_Date('01/09/1980','mm/dd/yyyy'));
INSERT INTO orders
VALUES (3,To_Date('01/10/1980','mm/dd/yyyy'));
And here is how I would like to use my function:
SELECT orders.ord_no
, orders.plan_start_dt
, add_mdays(orders.plan_start_dt, -3) AS prep_date
FROM orders
Thus, the function would allow me to return, for every order in my orders table, the date that is 3 workdays (mdays) prior to the plan start date of each order.
Am I going about this the right way? Do I need to create a function to do this, or is there a way for me to incorporate my query (that does my mday calculation) into the sample query above (eliminating the need to create a function)?
Thanks much in advance!
Edited by: user11033437 on Feb 2, 2010 8:55 AM
Fixed a couple typos in the last insert statements
Edited by: user11033437 on Feb 2, 2010 9:31 AM (fixed some syntax in the function)