Joe Celko s SQL for Smarties - Advanced SQL Programming P51. In the SQL database community, Joe Celko is a well-known columnist and purveyor of valuable insights. In Joe Celko's SQL for Smarties: Advanced SQL Programming, he picks up where basic SQL training and experience leaves many database professionals and offers tips, techniques, and explanations that help readers extend their capabilities to top-tier SQL programming. Although Celko denies that the book is about database theory, he nevertheless alludes to theory often to buttress his practical points. This title is not for novices, as the author points out. Instead, its intended audience. | 472 CHAPTER 21 AGGREGATE FUNCTIONS then the MOD functions determines whether the count was odd or even. I present this version of the query first because this is how I developed the answer. We can do a much better job with a little algebra and logic SELECT CASE MIN SIGN nbr WHEN 1 THEN EXP SUM LN nbr all positive numbers WHEN 0 THEN 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 ELSE END ELSE CAST NULL AS FLOAT END AS big_pi FROM NumberTable For this solution 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. You might also have problems with data types. The SIGN function should return an INTEGER but might return the same data type as its parameter. The ln function should cast nbr to float but again beware. 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 subcases 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. Itzik Ben-Gan had problems implementing this in SQL Server and these issues are worth passing along in case your SQL product also has them. The query as written returns a domain error in SQL Server. It should not have done so if the result expressions in the case expression had been evaluated after 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
