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
