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@...>
To: <firebird-support@yahoogroups.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@yahoogroups.com] On Behalf Of Gustavo
Sent: 9. november 2007 02:48
To: firebird-support@yahoogroups.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 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 2645 (20071108) __________

Este mensaje ha sido analizado con NOD32 antivirus system
http://www.nod32.com