Re: Writing aggregate functions in stored functions?

From: Jeff <jeff_at_work.com>
Date: Fri, 27 Jul 2001 13:13:47 GMT
Message-ID: <9jrpeb$gm5$1@cronkite.cc.uga.edu>

In article <9jr3k8$b5c$1_at_news.chatlink.com>, "Jerzy Tomasik" <tomasik_at_datapacket.net> wrote:
>Is it possible to develop an aggregate function (e.g. SUM, AVG, MAX, MIN)
>as a stored function (or any other way). I would like to be able to perform
>queries like:
>
>SELECT dept_no, MIN(sal), MEDIAN(sal), MAX(sal) FROM EMP
>GROUP BY dept_no;

Not according to Oracle. You can only use stored functions in a SQL expression and functions must be single-row, not group functions.

You could accomplish practically the same thing by passing the select statement (minus the Median()) to a function that used it in a dynamic SQL cursor to derive the median values and return the data via a PL/SQL table of records for output... or just output directly from within a procedure (instead of using a function). Received on Fri Jul 27 2001 - 08:13:47 CDT