Re: Divide by zero...

Robert Creager wrote:

> When grilled further on (Tue, 30 Sep 2003 09:26:19 -0400),
> Mike Leahy <mgleahy(at)fes(dot)uwaterloo(dot)ca> confessed:
>
>> I have a query that calculates various using variables from a survey
>> database. As with any survey, there are many instantces of null values. I'm
>> wondering if there is any way to escape the error caused by dividing by zero
>> or null values. The specific message i get is:
>>
>> ERROR: floating point exception! The last floating point operation either
>> exceeded legal ranges or was a divide by zero
>>
>> Is there a simple trick that won't make my queries excessively complex?
>
> I believe CASE and COALESCE will solve your problem. Something like this:
>
> SELECT CASE COALESCE( denom, 0.0 )
> WHEN 0.0 THEN 0.0
> ELSE COALESCE( num, 0.0 ) / denom
> END
> FROM some_table;

Definitely not. The result of a division by zero is undefined, and that
has a good reason. You cannot substitute it with zero or any other
explicit value without rendering your whole computation absurd. Look at
this simple example:

Let 2a = b | * 2
4a = 2b | + 10a
14a = 2b + 10a | - 7b
14a - 7b = 10a - 5b | ()
7 (2a - b) = 5 (2a - b) | / (2a - b)
7 = 5

Everything is fine, just that the division by (2a - b) is not allowed
because 2a = b and thus (2a - b) = 0. This demonstrates well that
division by zero only leads to nonsense, and nothing else. So please
change the 0.0 case to return NULL instead.

Jan

--
#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#================================================== JanWieck(at)Yahoo(dot)com #