Helen:

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:

UPDATE PMOVCC
SET MONTOPEND = NULLIF(ROUND(COALESCE(MONTOPEND,0) - 34.27 , 8) , 0)
WHERE (CLAVEMOVIM = 'XXX')


Do you think this will work all right?

Gustavo

----- Original Message -----
From: "Helen Borrie" <helebor@...>
To: <firebird-support@yahoogroups.com>
Sent: Wednesday, November 07, 2007 7:45 PM
Subject: Re: [firebird-support] Rounding error


> 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
>
>
> ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
>
> Visit http://www.firebirdsql.org and click the Resources item
> on the main (top) menu. Try Knowledgebase and FAQ links !
>
> Also search the knowledgebases at http://www.ibphoenix.com
>
> ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
>
> Yahoo! Groups Links
>
>
>
>
>
> __________ Información de NOD32, revisión 2642 (20071106) __________
>
> Este mensaje ha sido analizado con NOD32 antivirus system
> http://www.nod32.com
>
>