My question is a general one regarding if and how I can refer to a database column in a select statement from within a user-defined function. I know I can write a select statement in my function (or any PL/SQL procedure). In my case, I am calling my function as part of a select statement, and would like to refer to columns in the selected table directly, rather than including them as parameters to my function. I will be calling the function a number of times, and only one of its parameters will change.. so rather than passing 3 inputs, I'd prefer to pass one.
Here are the specifics:
I have created a user-defined function to calculate the number of days between two dates that fall within a quarter.
The function works fine, but I would like to be able to reference columns that are in the table.
As you will see in the code, my input variables are DT1, DT2, and QE, representing my two columns, and a constant quarter-end date, respectively.
I derive QB, as the quarter-begin date.
The function works fine, but when I call the function, I need to pass the two columns as inputs for DT1 and DT2 every time, in addition to the value for QE.
I have tried to find documentation on how (and if) I can write the function to refer to columns in the calling select statement, to no avail.
I'm using Oracle 8.1.7.
Function code (current version):
CREATE OR REPLACE FUNCTION DAYS_IN_Q(DT1 in date, DT2 in date, QE in date) RETURN number
AUTHID CURRENT_USER AS
QB date := trunc(QE,'Q');
if DT1>QE or DT2<QB
then DAYS := 0;
else DAYS := least(QE,DT2) - greatest(QB,DT1);
,DAYS_IN_Q(START_DATE,END_DATE,to_date('2009-06-30','YYYY-MM-DD')) as "DAYS_IN_Q209"
,DAYS_IN_Q(START_DATE,END_DATE,to_date('2009-09-30','YYYY-MM-DD')) as "DAYS_IN_Q309"
--What I want to do is simply my function so when I call it, it looks like:
--,DAYS_IN_Q(to_date('2009-09-30','YYYY-MM-DD')) as "DAYS_IN_Q309"
--(Obviously I need to write it so it only requires one parameter - That's not my problem.)