Solved

Column reference in Oracle user function

Posted on 2009-07-01
9
423 Views
Last Modified: 2013-12-19
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

DAYS number;

QB date := trunc(QE,'Q');

begin 

if DT1>QE or DT2<QB 

  then DAYS := 0;

  else DAYS := least(QE,DT2) - greatest(QB,DT1);

end if;

RETURN DAYS;

end;
 
 
 

--Select statement:

select

 START_DATE

,END_DATE

,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.)

from MY_TABLE

Open in new window

0
Comment
Question by:DuMonde
  • 5
  • 4
9 Comments
 
LVL 31

Expert Comment

by:awking00
ID: 24764325
Given that the values in those columns are necessary to do the calculation, they need to be passed in as parameters to the function. What is it that bothers you about including them?
0
 

Author Comment

by:DuMonde
ID: 24765129
Thank you for your response.
Many things may be necessary for a calculation, but that doesn't imply that they must be included as arguments.
They could be selected within the procedure itself in a separate select statement, for example.

Why I would prefer to not have to specify these two parameters is because, as I said, I'd be passing the same value every time I call the function. The only value that would change is the third parameter, which in my case is a quarter-end date. The other two parameters are effectively constants for each row that is selected.
My code will be simpler if I can pass just what in fact changes, e.g.:

select
START_DATE
,END_DATE
,DAYS_IN_Q(to_date('2009-03-31','YYYY-MM-DD'))  as "DAYS_IN_Q109"
,DAYS_IN_Q(to_date('2009-06-30','YYYY-MM-DD'))  as "DAYS_IN_Q209"
,DAYS_IN_Q(to_date('2009-09-30','YYYY-MM-DD'))  as "DAYS_IN_Q309"
from MY_TABLE

instead of having to include the columns START_DATE and END_DATE as arguments in my function call, as I do now:
select
START_DATE
,END_DATE
,DAYS_IN_Q(START_DATE,END_DATE,to_date('2009-03-31','YYYY-MM-DD'))  as "DAYS_IN_Q109"
,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"
from MY_TABLE

The code works fine; I simply want to keep my code as lean as possible.  As I mentioned as well, I'd like to understand whether this capability exists in general, beyond just this application.
My question remains, then, is whether Oracle's PL/SQL language provide a mechanism to reference a column that is part of the select statement in which the function is called.
Thank you very much!
0
 
LVL 31

Expert Comment

by:awking00
ID: 24784646
The problem is that you're not passing in a value, you're passing in an expression (in this case two attributes) that are needed to do a calculation along with a fixed value (the QE parameter) that is also needed for each record. If you want to make the select statement "leaner", you could pass in the QE parameter simply as '200903' and modify your function to convert that to the correct date using variable_QE := last_day(to_date(QE,'yyyymm'));
0
 

Author Comment

by:DuMonde
ID: 24795335
Hi again, awking00,
Thanks again for your thoughts.
It's funny you suggested supplying just year and month. I actually made that change this weekend!

So in sum, are you saying that there's no way to reference, from within a function, a column that is selected in the calling select statement?
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 31

Expert Comment

by:awking00
ID: 24797569
It could be done within a procedure, but not within a function as far as I can tell.
0
 

Author Comment

by:DuMonde
ID: 24797660
What would it look like syntactically within a procedure?
0
 
LVL 31

Expert Comment

by:awking00
ID: 24797928
See attached.
procedure.txt
0
 
LVL 31

Accepted Solution

by:
awking00 earned 250 total points
ID: 24797939
By the way, I think a procedure would be a lot more cumbersome than including the two column names in your function call.
0
 

Author Closing Comment

by:DuMonde
ID: 31598986
Thanks again very much for your thoughts on this.
I agree with you that passing the dates is simpler.
Best regar
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Generate HTML charts from SQL 4 51
Oracle Finace 3 43
Need help with Oracle sql and I am using Aqua studio. 8 61
Oracle Pivot 2 32
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

708 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now