[ENG] query problem

I've a query problem:
SELECT person.IDProprietary, FirstName, LastName, StartDate, abos.IDAbo,
billpro.IDBill, DateBill, AmountBill, SUM(paymentpro.AmountPayment) as
totalpayment
from person
inner join typevendeur on person.IDTypeVendeur =
typevendeur.IDTypeVendeur
left join abos on person.IDProprietary = abos.IDProprietary
left join billpro on abos.IDAbo = billpro.IDAbo
left join paymentpro on billpro.IDBill = paymentpro.IDBill
WHERE typevendeur.IDTypeVendeur > 1
group by person.IDProprietary
HAVING ((AmountBill is NULL) or ((AmountBill
+(0.05*ROUND(((AmountBill) * 0.076)/0.05,0))) > (totalpayment-5)) or
(totalpayment is NULL))
ORDER BY ContractDate, IDProprietary, FirstName

the result include a record where totalpayment = the calculation in the
having clause. Also the amountbill is not null and totalpayment also is not
null.

So why the result include the record that shouldn't be there ? any idea ?

Re: [ENG] query problem

Bob Bedford wrote:
> I've a query problem:
> SELECT person.IDProprietary, FirstName, LastName, StartDate, abos.IDAbo,
> billpro.IDBill, DateBill, AmountBill, SUM(paymentpro.AmountPayment) as
> totalpayment
> from person
> inner join typevendeur on person.IDTypeVendeur =
> typevendeur.IDTypeVendeur
> left join abos on person.IDProprietary = abos.IDProprietary
> left join billpro on abos.IDAbo = billpro.IDAbo
> left join paymentpro on billpro.IDBill = paymentpro.IDBill
> WHERE typevendeur.IDTypeVendeur > 1
> group by person.IDProprietary
> HAVING ((AmountBill is NULL) or ((AmountBill
> +(0.05*ROUND(((AmountBill) * 0.076)/0.05,0))) > (totalpayment-5)) or
> (totalpayment is NULL))
> ORDER BY ContractDate, IDProprietary, FirstName
>
> the result include a record where totalpayment = the calculation in the
> having clause. Also the amountbill is not null and totalpayment also is not
> null.
>
> So why the result include the record that shouldn't be there ? any idea ?

I did not check the condition in detail, but I see, that you are doing
floating point calculations. In this context the rule exists, that
floating point values are never equal, they only happen to be "very
close to each other".
The values you see are shortened string representations of floating
point values and those are indeed equal.

Try the following:
CAST both sides of the unequation to a DECIMAL type with enough decimal
places to fulfill your equality requirements. For example DECIMAL(10,2)
for money calculations.

HTH and Greetings
Kai

Re: [ENG] query problem

Thanks for the answer Kai

> I did not check the condition in detail, but I see, that you are doing
> floating point calculations. In this context the rule exists, that
> floating point values are never equal, they only happen to be "very close
> to each other".
> The values you see are shortened string representations of floating point
> values and those are indeed equal.
>
> Try the following:
> CAST both sides of the unequation to a DECIMAL type with enough decimal
> places to fulfill your equality requirements. For example DECIMAL(10,2)
> for money calculations.

In fact I've seen an error in the query as I removed a margin ($marge=5)
where I'd to add it instead.

Thanks to your answer as I didn't know the decimal calculation isn't very
precise and would take this into account when working with float numbers.

Cheers

Bob