At 05:26 AM 8/11/2007, you wrote:

>Hello:
>
>I use Delphi 5, FireBird 2.0 and IBX and I have the following problem.
>
>I have a table named PMOVCC with a field MONTOPEND of type DOUBLE PRECISION and sometimes, when after an UPDATE statement it has to be null, it has a value like 7,105427357601E-15.
>
>I will give an example.
>
>1. In one record of the table, the field MONTOPEND has the value 34.27.
>
>2. I execute the following statement:
>
>UPDATE PMOVCC
>SET MONTOPEND = NULLIF(COALESCE(MONTOPEND,0) - 34.27,0)
>WHERE (CLAVEMOVIM = 'XXX')
>
>3. Now, the field should be NULL, but it has the value 7,105427357601E-15.
>
>It´s a strange thing because this doesn´t happen always. Sometimes it works good!
>
>
>My questions are:
>
>1. Is there a way to avoid this rounding error?

It's just an intrinsic part of working with floating point values. You can never assume that a floating point number is *exact*. That includes zero.

>2. Is there a way to configure FireBird not to use more than N decimal digits in any calculation? If this coud be done, I could configure not to use more than 8 or 9 digits and then, even if there is a rounding error, the result will be right.

If you want to store and work with fixed point values then don't use floating point data!

During calculations you can cast floats and double precision numbers to fixed decimal numbers (numeric or decimal) but it's not necessarily going to win the war. In your example, if both of the 34.27 values were cast as numeric in the expression, the result would be a numeric and zero would be exactly zero. Then, NULLIF() should treat (zero==zero) as true and store null.

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, so you might win one way and lose the other. You just have to be extremely careful when using expressions to manipulate floating-point numbers.

./heLen