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

```