Robert Creager wrote:
When grilled further on (Tue, 30 Sep 2003 09:26:19 -0400), Mike Leahy <[EMAIL PROTECTED]> 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 = 5Everything 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. # #================================================== [EMAIL PROTECTED] #
---------------------------(end of broadcast)--------------------------- TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faqs/FAQ.html
