This is not a Firebird thing. It always exists when you use floating
point numbers.

See http://en.wikipedia.org/wiki/Floating_point#Accuracy_problems for
more information...

-steve

Gustavo wrote:

>
> Svein:
>
> I understand. MONTOPEND is an amount of money. So perhaps it would be
> a good
> idea to use a NUMERIC field. But this field is only an example and I have
> fields which are quantities and in some cases I need DOUBLE PRECISION
> fields.
>
> I agree with you that, in theory, to say that a line is 100 meters or
> 99.99957 meters is "almost" the same (but not exactly the same).
> Anyway, I
> think it´s an error of FireBird that if the DP field MONTOPEND has a
> value
> of 34.27 and after executing the statement bellow, then the field has the
> value 7,105427357601E-15 instead of null.
>
> UPDATE PMOVCC
> SET MONTOPEND = NULLIF(COALESCE(MONTOPEND,0) - 34.27,0)
> WHERE (CLAVEMOVIM = 'XXX')
>
> 34.27 - 34.27 shoud be 0 even if it is a DOUBLE PRECISSION field.
>
> I have a lot of tables already defined with DP fields and I think it´s
> not
> convenient now to ALTER them to NUMERIC fields. I think what I can do
> is to
> replace the NULLIF using the ROUND function as follows (when I install
> FireBird 2.1):
>
> UPDATE PMOVCC
> SET MONTOPEND = NULLIF(ROUND(COALESCE(MONTOPEND,0) - 34.27,8),0)
> WHERE (CLAVEMOVIM = 'XXX')
>
> Without installing FireBird 2.1, in some of the fields, I can use CAST as
> follows:
>
> UPDATE PMOVCC
> SET MONTOPEND = NULLIF(CAST(COALESCE(MONTOPEND,0) - 34.27 AS
> NUMERIC(18,8)),0)
> WHERE (CLAVEMOVIM = 'XXX')
>
> Gustavo
>
> ----- Original Message -----
> From: "Svein Erling Tysvær" <svein.erling.tysvaer@...
> <mailto:svein.erling.tysvaer%40kreftregisteret.no>>
> To: <firebird-support@yahoogroups.com
> <mailto:firebird-support%40yahoogroups.com>>
> Sent: Friday, November 09, 2007 6:06 AM
> Subject: RE: [firebird-support] Rounding error
>
> 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
>
> MyLength DOUBLE PRECISION
> 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).
>
> HTH,
> Set
>
> -----Original Message-----
> From: firebird-support@yahoogroups.com
> <mailto:firebird-support%40yahoogroups.com>
> [mailto:firebird-support@yahoogroups.com
> <mailto:firebird-support%40yahoogroups.com>] On Behalf Of Gustavo
> Sent: 9. november 2007 02:48
> To: firebird-support@yahoogroups.com
> <mailto:firebird-support%40yahoogroups.com>
> Subject: Re: [firebird-support] Rounding error
>
> 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
>
> ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
>
> Visit http://www.firebirdsql.org <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
> <http://www.ibphoenix.com>
>
> ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
>
> Yahoo! Groups Links
>
> __________ Información de NOD32, revisión 2645 (20071108) __________
>
> Este mensaje ha sido analizado con NOD32 antivirus system
> http://www.nod32.com <http://www.nod32.com>
>
>