secondary school mathematics that was almost useful

For the first time in my career I came across the need to make use of a PRODUCT() SQL aggregate function today. To my dismay SQL Server 8 doesn’t have such a function and so I figured out it can be simulated by use of logarithims and the SUM() function (since (log10(a) + log10(b))^10 = a * b) Therefore my query could read:

SELECT POWER(10, SUM(LOG10(value)))
FROM table

But this query doesn’t work if there are negative numbers in the PRODUCT() list because LOG10(x) where x<0 is undefined. Guess what, there’s a bunch of negative numbers in my list. Arghh … So this complicates matters a bit. But not very much. We can simply do the same as before but now on the ABS() value of the number but additionally count the number of times we have -ve numbers and then if that count is odd then the result is negative. Otherwise it’s positive. So to do this we will need to:

SELECT COUNT(CASE WHEN CONVERT(INT,SIGN(value))<0 THEN 1 ELSE NULL END)
FROM table

Which will give us a count of the number of negative values. If we take the modulo two of this number then we will get a zero if the overall result should be positive (i.e. it's even) and a one if it should be negative (i.e. it's odd). The rest is easy. Since, for y=-2x+1 then when x=0 or x=1 we will have 1 and -1 which we can multiply our result by to get the correct sign! Here is the full query:

SELECT POWER(10, SUM(LOG10(ABS(value)))) *
(-2 * (COUNT(CASE WHEN CONVERT(INT,SIGN(value))<0 THEN 1 ELSE NULL END) % 2) + 1)
FROM table

I wish I could thank my secondary school mathematics teacher Mrs Bishop for this mathematical insight. But unfortunately she was crap, so I won't. Finally, why was it only almost useful? Well ingenious as I think it is it's also a bit twisted. A lot of clarity has been sacrificed for the sake of a performance gain and this isn't usually good. If the tables were very large then it might be worth using the above instead of the cursor that I was trying to replace. But only then. Which begs the question why don't SQL vendors supply a PRODUCT() function? Got to be almost a cut and paste job. I see that in SQL Server 2005 one can make one's own aggregates via the CLR. I almost like this idea but I also fear all manner of nasty / filthy code being inserted into the server. How long before someone writes a SQL Server 2005 virus?