Re: user defined column functions?

>> Are there any discussions, plans, etc. for userdefined column
functions in future SQL versions (or do they already exist somewhere and I'm not looking hard enough)? <<

We did add CUBE and ROLLUP to SQL-99, but you can do a lot with what you have now.

Here is a version of the aggregate product function in SQL. You will need to have the logorithm and exponential functions. They are not standards, but they are very common.

The idea is that there are three special cases - all positive numbers, one or more zeroes, and some negative numbers in the set. You can find out what your situation is with a quick test on the sign() of the minimum value in the set.

Within the case where you have negative numbers, there are two sub-cases: (1) an even number of negatives or (2) an odd number of negatives. You then need to apply some High School algebra to determine the sign of the final result.

SELECT CASE MIN (SIGN(nbr))

       WHEN 1 THEN EXP(SUM(LN(nbr))) -- all positive numbers 
       WHEN 0 THEN 0.00                 -- some zeroes 
       WHEN -1                          -- some negative numbers 
       THEN (EXP(SUM(LN(ABS(nbr)))) 
             * (CASE WHEN 
                     MOD (SUM(ABS(SIGN(nbr)-1)/ 2)), 2) = 1
                     THEN -1.00 ELSE 1.00 END) 
       ELSE NULL END AS big_pi

SELECT CASE MIN (SIGN (nbr))

       WHEN 1 THEN EXP (SUM (LN (nbr))) -- all positive numbers
       WHEN 0 THEN 0.00                 -- some zeros
       WHEN -1                          -- some negative numbers
       THEN (EXP (SUM (LN (ABS(nbr))))
             * (CASE WHEN
                     MOD (SUM (ABS (SIGN(nbr)-1)/ 2)), 2) = 1
                     THEN -1.00 ELSE 1.00 END)
       ELSE NULL END AS big_pi

You will need to have the logarithm, exponential, mod and sign functions in your SQL product. They are not standards, but they are very common.

The idea is that there are three special cases - all positive numbers, one or more zeros, and some negative numbers in the set. You can find out what your situation is with a quick test on the sign() of the minimum value in the set.

Within the case where you have negative numbers, there are two sub-cases: (1) an even number of negatives or (2) an odd number of negatives. You then need to apply some High School algebra to determine the sign of the final result.

Itzak Ben-Gan had problems in implementing this in SQL Server that are worth passing along in case your SQL product also has them. The query as written returns a domain error in SQL Server, even though it should not had the result expressions in the CASE expression been evaluated <i>after<i> the conditional flow had performed a short circuit evaluation. Examining the execution plan of the above query, it looks like the optimizer evaluates all of the possible result expressions in a step prior to handling the flow of the CASE expression.

This means that in the expression after WHEN 1 ... the LN() function is also invoked in an intermediate phase for zeros and negative numbers, and in the expression after WHEN -1 ... the LN(ABS()) is also invoked in an intermediate phase for 0's. This explains the domain error.

To handle this, I had to use the ABS() and NULLIF() functions in the positive numbers when CLAUSE, and the NULLIF() function in the negative numbers when CLAUSE:

   ...
   WHEN 1 THEN EXP(SUM(LN(ABS(NULLIF(result, 0.00))))) and

   ...
   WHEN -1
   THEN EXP(SUM(LN(ABS(NULLIF(result, 0.00)))))

• CASE ...