Re: Divide by zero...

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;

Cheers,
Rob

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