that I run regularly.
I have found a problem that when I try to update a table with a
calculated figure a rounding error occurs.
This problem can be shown by running the following code:
UPDATE NumericVal
SET ValExpected = 0.72139753801593054
WHERE NumericVal.ValItemCode = 2441 AND ValTPCode = 4159
SELECT ValExpected
FROM NumericVal
WHERE NumericVal.ValItemCode = 2441 AND ValTPCode = 4159
ValExpected returns the value 0.72139752, although it should be
identical to entered value which would round to 0.72139754.
I know it's only a small difference but it is causing greater errors in
later calculations.
Is there any explanation as to why this is happening and how can I
resolve the issue?What are the datatypes? Without knowing the datatypes all we an do is
speculate.
I speculate that you are using FLOAT, or REAL.
FLOAT and REAL are internally binary. They store data to the right of
the decimal point as binary fractions: 1/2, 1/4, 1/8, 1/16, 1/32, etc.
Just as the decimal system can not accurately store 1/3, the binary
system can not store some numbers that store accurately in the decimal
system. This is why REAL and FLOAT are described in the documentation
as: "Approximate number data types for use with floating point numeric
data. Floating point data is approximate; not all values in the data
type range can be precisely represented."
Roy Harvey
Beacon Falls, CT
On 28 Jun 2006 04:49:50 -0700, "AdamHCC"
<adam.roberts@.healthcarecommission.org.uk> wrote:
>I am trying to discover the source of an error that occurs in some code
>that I run regularly.
>I have found a problem that when I try to update a table with a
>calculated figure a rounding error occurs.
>This problem can be shown by running the following code:
>UPDATE NumericVal
>SET ValExpected = 0.72139753801593054
>WHERE NumericVal.ValItemCode = 2441 AND ValTPCode = 4159
>SELECT ValExpected
>FROM NumericVal
>WHERE NumericVal.ValItemCode = 2441 AND ValTPCode = 4159
>
>ValExpected returns the value 0.72139752, although it should be
>identical to entered value which would round to 0.72139754.
>I know it's only a small difference but it is causing greater errors in
>later calculations.
>Is there any explanation as to why this is happening and how can I
>resolve the issue?|||Adam
ValExpected has a DECIMAL /FLOAT/REAL datatype ?
"AdamHCC" <adam.roberts@.healthcarecommission.org.uk> wrote in message
news:1151495390.107042.281570@.d56g2000cwd.googlegroups.com...
>I am trying to discover the source of an error that occurs in some code
> that I run regularly.
> I have found a problem that when I try to update a table with a
> calculated figure a rounding error occurs.
> This problem can be shown by running the following code:
> UPDATE NumericVal
> SET ValExpected = 0.72139753801593054
> WHERE NumericVal.ValItemCode = 2441 AND ValTPCode = 4159
> SELECT ValExpected
> FROM NumericVal
> WHERE NumericVal.ValItemCode = 2441 AND ValTPCode = 4159
>
> ValExpected returns the value 0.72139752, although it should be
> identical to entered value which would round to 0.72139754.
> I know it's only a small difference but it is causing greater errors in
> later calculations.
> Is there any explanation as to why this is happening and how can I
> resolve the issue?
>|||It looks like ValExpected is declared to be of type REAL, which has limited
precision. To preserve the exact value, use a DECIMAL type with appropriate
precision and scale, or to keep about 15-16 decimal places of accuracy and
allow a wider range, use FLOAT instead of REAL.
Steve Kass
Drew University
AdamHCC wrote:
>I am trying to discover the source of an error that occurs in some code
>that I run regularly.
>I have found a problem that when I try to update a table with a
>calculated figure a rounding error occurs.
>This problem can be shown by running the following code:
>UPDATE NumericVal
>SET ValExpected = 0.72139753801593054
>WHERE NumericVal.ValItemCode = 2441 AND ValTPCode = 4159
>SELECT ValExpected
>FROM NumericVal
>WHERE NumericVal.ValItemCode = 2441 AND ValTPCode = 4159
>
>ValExpected returns the value 0.72139752, although it should be
>identical to entered value which would round to 0.72139754.
>I know it's only a small difference but it is causing greater errors in
>later calculations.
>Is there any explanation as to why this is happening and how can I
>resolve the issue?
>
>|||ValExpected is declared as a real variable.
Why when storing the value would it store 0.72139752 rather than
rounding it to 0.72139754 or even truncating it to 0.72139753?
NOTE: I get the same result if I use the fraction as follows:
UPDATE NumericVal
SET ValExpected = CAST (3985 AS REAL) / CAST (5524 AS REAL)
WHERE NumericVal.ValItemCode = 2441 XAND ValTPCode = 4159|||SORRY:
The SQL Script above should read:
UPDATE NumericVal
SET ValExpected = CAST (3985 AS REAL) / CAST (5524 AS REAL)
WHERE NumericVal.ValItemCode = 2441 AND ValTPCode = 4159|||Adam,
A REAL just can't represent any value within 0.00000001 of
0.72139754.
declare @.r real
set @.r = 0.72139754
select @.r, cast(@.r as binary(4))
set @.r = 0.72139755
select @.r, cast(@.r as binary(4))
-- Results
0.72139752 0x3F38AD82
0.72139758 0x3F38AD83
The two binary values here are "adjacent" REAL values, and
the decimal output is rounded for display purposes to 8 places.
A REAL can't represent any value between these two.
Eight decimal places are shown because there are REAL values
that are different numbers but that have the same 7-place decimal
rounding. The tradeoff is then that there are 8-place decimal values
that are not the rounded version of any REAL. The decimal equivalent
of a REAL's precision is just over 7 digits.
SK
AdamHCC wrote:
>ValExpected is declared as a real variable.
>Why when storing the value would it store 0.72139752 rather than
>rounding it to 0.72139754 or even truncating it to 0.72139753?
>
>NOTE: I get the same result if I use the fraction as follows:
>UPDATE NumericVal
>SET ValExpected = CAST (3985 AS REAL) / CAST (5524 AS REAL)
>WHERE NumericVal.ValItemCode = 2441 XAND ValTPCode = 4159
>
>|||Thank you for all of your help. However I am still
.If REAL can only store that the number to seven places, why then does
it dump the following number into excel; 0.721397519111633 ?
This has far more digits that can be stored in REAL and is totally
incorrect after the first 7 decimal places. Why return another 8
decimal places that are incorrect? Surely common sense dictates that
this should not happen.|||Adam,
I can only guess, but I imagine that the connection between SQL Server
and Excel doesn't have the sophistication to distinguish between source
values that are 4-byte floating point and source values that are 8-byte
floating point, so Excel displays all "number" values to 15 decimal places
by default, since that is roughly the maximum that could be meaningful
without knowing more about the source type.
To be fair, the extra 8 decimal places are not "incorrect." Once the
value of a real number is stored in SQL Server, it is an exact value.
Nothing is stored along with the number to indicate whether precision
was lost when the number was stored.
SK
AdamHCC wrote:
>Thank you for all of your help. However I am still
.>If REAL can only store that the number to seven places, why then does
>it dump the following number into excel; 0.721397519111633 ?
>This has far more digits that can be stored in REAL and is totally
>incorrect after the first 7 decimal places. Why return another 8
>decimal places that are incorrect? Surely common sense dictates that
>this should not happen.
>
>
No comments:
Post a Comment