Re: user defined column functions?

On Wed, 24 Apr 2002 12:12:18 -0700, "Joe \"Nuke Me Xemu\" Foster" <joe_at_bftsi0.UUCP> wrote:

>"--CELKO--" <71062.1056_at_compuserve.com> wrote in message <news:c0d87ec0.0204241009.6396150a_at_posting.google.com>...
>
>
>> 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
>> FROM NumberTable;
>
>Couldn't MIN(SIGN(nbr)) be -1 even if one or more zeroes is present?

Probably why domain errors occur.
The logic is valid, however.
Does this work?

SELECT CASE MIN(ABS(SIGN(nbr)))

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