• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 378
  • Last Modified:

user defined function

Hi,
The following restriction has been mentioned in the oracle documentation while invoking an user defined function from an sql expression:
•It must be a row function, not a column (group) function; that is, its argument cannot be an entire column.

Can somebody please explain this with an example? How can the entire column be passed as an argument to an user defined function?

Thanks in advance.
0
subratoc
Asked:
subratoc
  • 3
1 Solution
 
sdstuberCommented:
aggregates,  like MAX, MIN, AVG  work on a column for all rows in a group
0
 
sdstuberCommented:
as opposed to something like  TO_CHAR  which works on the value within a single row
0
 
subratocAuthor Commented:
Hi sdstuber,
Thanks for your responses but can you please explain this in the context of an user defined function?
MAX, MIN, AVG  are aggregate functions predefined by oracle but
on reading the documenation "argument cannot be an entire column" I was wondering
how can the entire column be passed as an argument to an user defined function?

e.g. I run the query,

select col1, fn_user_def(col1) from tab1;

No problems. I am passing the value of col1 here for every row to the function fn_user_def.
But how is it possible to pass the entire column to fn_user_def
as if I am calling "fn_user_def(entire_column_parameter)"?
0
 
sdstuberCommented:
I'm not sure what you mean?

That is exactly what I'm trying say.

you can ONLY pass an entire column to a function if that function is an aggregate.
and, when you do that,  it's implicit, the aggregate knows how to process the column
0

Featured Post

Prep for the ITIL® Foundation Certification Exam

December’s Course of the Month is now available! Enroll to learn ITIL® Foundation best practices for delivering IT services effectively and efficiently.

  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now