Thread: Divide by zero...

All...

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?

Many thanks.

Mike

----------------------------------------
This mail sent through www.mywaterloo.ca

When grilled further on (Tue, 30 Sep 2003 09:26:19 -0400),
Mike Leahy <mgleahy@fes.uwaterloo.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;

Cheers,
Rob

--
 07:48:16 up 60 days, 19 min,  4 users,  load average: 2.32, 2.78, 2.97

Robert Creager wrote:

> When grilled further on (Tue, 30 Sep 2003 09:26:19 -0400),
> Mike Leahy <mgleahy@fes.uwaterloo.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@Yahoo.com #