Re: Aggregate PL/SQL function

>> I would like to write a PL/SQL function to return aggregate results
such as count(), min(), max(), etc. Does anyone know how to do this? <<

Let me show you a trick from my book, SQL FOR SMARTIES that can save you the cost of procedural code.

--CELKO-- 21.5.2.2 The PROD() Aggregate Function by Logorithms

Roy Harvey, another SQL guru who answers questions on CompuServe, found a different solution, which only someone old enough to remember slide rules and that we can multiply by adding logs. The nice part of this solution is that you can also use the DISTINCT option in the SUM() function.

But there are a lot of warnings about this approach. Some older SQL implementation might have trouble with using an aggregate function result as a parameter. This has always been part of the standard, but some SQL products use very different mechanisms for the aggregate functions.

Another, more fundamental problem is that the log of zero or less is undefined, so your SQL might return a NULL or an error message. You will also see some SQL products that use LN() for the natural log and LOG10() for the logarithm base ten and some SQLs that use LN (<parameter>, <base>) for a general logarithm function.

Given all those warnings, the expression for the product of a column from logarithm and exponential functions is:

SELECT ((EXP (SUM (LN (CASE WHEN nbr = 0.00

                            THEN NULL
                            ELSE ABS(nbr) END))))
  * (CASE WHEN MIN (ABS (nbr)) = 0.00
          THEN 0.00
          ELSE 1.00 END)
  * (CASE WHEN MOD (SUM (CASE WHEN SIGN(nbr) = -1
                               THEN 1
                               ELSE 0 END), 2) = 1
           THEN -1.00
           ELSE 1.00 END) AS Prod

The nice part of this is that you can also use the SUM (DISTINCT <expression>) option to get the equivalent of PROD (DISTINCT <expression>).

You should watch the data type of the column involved and use either integer 0 and 1 or decimal 0.00 and 1.00 as is appropriate in the CASE statements. It is worth studying the three CASE expressions that make up the terms of the Prod calculation.

The first CASE expression is to insure that all zeros and negative numbers are converted to a non-negative or NULL for the SUM() function, just in case your SQL raises an exception.

The second CASE expression will return zero as the answer if there was a zero in the nbr column of any selected row. The MIN(ABS(nbr)) is a handy trick for detecting the existence of a zero in a list of both positive and negative numbers with an aggregate function.

The third CASE expression will return minus one if there was an odd number of negative numbers in the nbr column. The innermost CASE expression uses a SIGN() function which returns +1 for a positive number, -1 for a negative number and 0 for a zero. The SUM() counts the -1 results then the MOD() functions determines if the count was odd or even.

As an aside, the book BYPASSES: A SIMPLE APPROACH TO COMPLEXITY by Z. A. Melzak (Wiley-Interscience, 1983, ISBN 0-471-86854-X), is short mathematical book on the general principle of conjugacy. This is the method of using a transform and its inverse to reduce the complexity of a calculation.

--CELKO-- Sent via Deja.com http://www.deja.com/
Before you buy. Received on Thu Jan 27 2000 - 21:57:08 CET