Subject RE: [firebird-support] Rounding error
Author Svein Erling Tysvær
Hi Gustavo!

Do you understand the difference between floating point and fixed point numbers? I'd say the cause of your problem is that you apply fixed decimal logic to floating point values! If I ask someone to give me a line that was 100 meters, it would be ridiculous to complain if I measured it to be 99.99957 metres. However, if I was to be given 10000 euro, and I got 9999.99, I would have reason to complain (at least in theory). The difference is that length is something approximate, i.e. floating point, whereas money can be measured exactly - i.e. fixed point. Speaking Firebird, this could be represented as

MyCurrency DECIMAL

You should treat these two types of numbers differently, DOUBLE PRECISION is approximate, whereas DECIMAL is exact. Using

WHERE MyCurrency = FixedValue

is fine, but with MyLength you should never do this. MyLength is approximate and you can never trust it to equal anything - you have to do something like

WHERE MyLength BETWEEN FixedValue - 0.001 and FixedValue + 0.001

I expect that using ROUND would be similar to BETWEEN (except that ROUND doesn't use indexes if used in WHERE clauses, except if you use expression indexes), but it is worth considering whether MONTOPEND really should be a floating point number (I have no idea what MONTOPEND means).


-----Original Message-----
From: [] On Behalf Of Gustavo
Sent: 9. november 2007 02:48
Subject: Re: [firebird-support] Rounding error


Thank you for your answer.

I don´t understand something. What do you mean when you say "However, if the
result of the evaluation is false, the non-zero value that you store back
into the DP column will be different to what it would be if the expression
had worked on floating-point values"?

I think I got an idea to solve this. I didn´t install FireBird 2.1 yet, but
I read it has a new UDF ROUND. So I think I can modify my UPDATE statement
using this function as follows:


Do you think this will work all right?