Re: Divide by zero...

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