List:General Discussion« Previous MessageNext Message »
From:Christian Mack Date:May 31 1999 4:05pm
Subject:Re: Double Comparison Questions
View as plain text  
Dave wrote:
> 
> Hi all,
> 
> I remember someone on this list mentioning a problem or potential problem
> when comparing doubles or reals in a where or having clause.
> 
> I am doing a select like:
> 
> select sum(a.amount) as balance, c.cust_id, c.last, c.first
> from customers c, main.ar_ledger a
> where
>     c.cust_id = a.cust_id &&
>     trial != 1 &&
>     status = 1 &&
>     pay_method = 2
>     group by a.cust_id
> having balance > 0
> order by c.last
> 
> This select INCLUDES rows wuith a balance of 0.00
> 
> In my amount field I am using a double(8,2) should I be using a decimal
> type? or does that even matter? I have got around the problem for now by
> using:
> 
> having sign(balance) = 1 instead of having balance > 0
> 
> which just returns rows with a positive balance.
> 
> But it still bugs me....
> 
> Can anyone point me to about when that discussion was taking place on the
> mail list and possibly a subject line that I may search for?
> 
> Also, is it recommended to use decimal as a type for real numbers? The only
> use my application has for them is for monetary purposes (accounting) so if
> decimal is the way to go then I will switch, providing alter table will not
> trash my existing values..
> 
> Any help is greatly appreciated
> 
> -Dave

Hi Dave

Why don't you check after beeing bigger or equal to 0.01 ?
Use:
SELECT 
	SUM(a.amount) AS balance
	, c.cust_id
	, c.last
	, c.first
FROM
	customers c
	, main.ar_ledger a
WHERE
	c.cust_id = a.cust_id
	AND trial != 1
	AND status = 1
	AND pay_method = 2
GROUP BY 
	a.cust_id
HAVING
	balance >= 0.01
ORDER BY
	c.last

Tschau
Christian

Thread
Double Comparison QuestionsDave29 May
  • Re: Double Comparison QuestionsChristian Mack1 Jun