Has anyone else noticed that SQL is handling Real numbers incorrectly, or am
I wrong at this finding?
The below query is an example. (you can try any number of decimal
multiplications, or even sometimes decimal additions, but only seems to
affect 'REAL' numbers - numeric, decimal, money and float seem to do just
fine)
SELECT convert(real,.11) * CONVERT(real,3)
this returns 0.32999998 instead of .33.
This might not be a problem inherent to SQL as I get simular problems
handling real numbers from a Java based application, so I'm not sure if this
problem might be Windows or hardware based. I did this same test on several
different machines, (dual Xeons and P4 laptops) and every time it returns th
e
incorrect result.This issue also applies to the float data type. Real and float data types
can only store approximate numeric data because some values cannot be stored
precisely. Use decimal or money when exact decimal values are required.
From the Books Online:
<Excerpt href="http://links.10026.com/?link=createdb.chm::/cm_8_des_04_82ic.htm">
Approximate numeric (floating-point) data consists of data preserved as
accurately as the binary numbering system can offer. Approximate numeric
data is stored using the float and real data types in SQL Server. For
example, because the fraction one-third in decimal notation is .333333
(repeating), this value cannot be represented precisely using approximate
decimal data. Therefore, the value retrieved from SQL Server may not be
exactly what was stored originally in the column. Additional examples of
numeric approximations are floating-point values ending in .3, .6, and .7.
</Excerpt>
Hope this helps.
Dan Guzman
SQL Server MVP
"Dimbit" <Dimbit@.discussions.microsoft.com> wrote in message
news:1A0E0F28-5244-4C16-80EB-FC9B08B4B8B0@.microsoft.com...
> Has anyone else noticed that SQL is handling Real numbers incorrectly, or
> am
> I wrong at this finding?
> The below query is an example. (you can try any number of decimal
> multiplications, or even sometimes decimal additions, but only seems to
> affect 'REAL' numbers - numeric, decimal, money and float seem to do just
> fine)
> SELECT convert(real,.11) * CONVERT(real,3)
> this returns 0.32999998 instead of .33.
> This might not be a problem inherent to SQL as I get simular problems
> handling real numbers from a Java based application, so I'm not sure if
> this
> problem might be Windows or hardware based. I did this same test on
> several
> different machines, (dual Xeons and P4 laptops) and every time it returns
> the
> incorrect result.|||use decimal or numeric
read "DATA TYPES" in BOL
decimal
Fixed precision and scale numeric data from -10^38 +1 through 10^38 –1.
numeric
Functionally equivalent to decimal.
- - - Approximate Numerics - - -
float
Floating precision number data with the following valid values: -1.79E + 308
through -2.23E - 308, 0 and 2.23E + 308 through 1.79E + 308.
real
Floating precision number data with the following valid values: -3.40E + 38
through -1.18E - 38, 0 and 1.18E - 38 through 3.40E + 38.
Aleksandar Grbic
MCDBA, Senior Database Administrator
"Dimbit" wrote:
> Has anyone else noticed that SQL is handling Real numbers incorrectly, or
am
> I wrong at this finding?
> The below query is an example. (you can try any number of decimal
> multiplications, or even sometimes decimal additions, but only seems to
> affect 'REAL' numbers - numeric, decimal, money and float seem to do just
> fine)
> SELECT convert(real,.11) * CONVERT(real,3)
> this returns 0.32999998 instead of .33.
> This might not be a problem inherent to SQL as I get simular problems
> handling real numbers from a Java based application, so I'm not sure if th
is
> problem might be Windows or hardware based. I did this same test on sever
al
> different machines, (dual Xeons and P4 laptops) and every time it returns
the
> incorrect result.
Showing posts with label real. Show all posts
Showing posts with label real. Show all posts
Monday, March 19, 2012
Incorrect Handling of Real Numbers
Has anyone else noticed that SQL is handling Real numbers incorrectly, or am
I wrong at this finding?
The below query is an example. (you can try any number of decimal
multiplications, or even sometimes decimal additions, but only seems to
affect 'REAL' numbers - numeric, decimal, money and float seem to do just
fine)
SELECT convert(real,.11) * CONVERT(real,3)
this returns 0.32999998 instead of .33.
This might not be a problem inherent to SQL as I get simular problems
handling real numbers from a Java based application, so I'm not sure if this
problem might be Windows or Hardware based. I did this same test on several
different machines, (dual Xeons and P4 laptops) and every time it returns the
incorrect result.
This issue also applies to the float data type. Real and float data types
can only store approximate numeric data because some values cannot be stored
precisely. Use decimal or money when exact decimal values are required.
From the Books Online:
<Excerpt href="http://links.10026.com/?link=createdb.chm::/cm_8_des_04_82ic.htm">
Approximate numeric (floating-point) data consists of data preserved as
accurately as the binary numbering system can offer. Approximate numeric
data is stored using the float and real data types in SQL Server. For
example, because the fraction one-third in decimal notation is .333333
(repeating), this value cannot be represented precisely using approximate
decimal data. Therefore, the value retrieved from SQL Server may not be
exactly what was stored originally in the column. Additional examples of
numeric approximations are floating-point values ending in .3, .6, and .7.
</Excerpt>
Hope this helps.
Dan Guzman
SQL Server MVP
"Dimbit" <Dimbit@.discussions.microsoft.com> wrote in message
news:1A0E0F28-5244-4C16-80EB-FC9B08B4B8B0@.microsoft.com...
> Has anyone else noticed that SQL is handling Real numbers incorrectly, or
> am
> I wrong at this finding?
> The below query is an example. (you can try any number of decimal
> multiplications, or even sometimes decimal additions, but only seems to
> affect 'REAL' numbers - numeric, decimal, money and float seem to do just
> fine)
> SELECT convert(real,.11) * CONVERT(real,3)
> this returns 0.32999998 instead of .33.
> This might not be a problem inherent to SQL as I get simular problems
> handling real numbers from a Java based application, so I'm not sure if
> this
> problem might be Windows or Hardware based. I did this same test on
> several
> different machines, (dual Xeons and P4 laptops) and every time it returns
> the
> incorrect result.
|||use decimal or numeric
read "DATA TYPES" in BOL
decimal
Fixed precision and scale numeric data from -10^38 +1 through 10^38 –1.
numeric
Functionally equivalent to decimal.
- - - Approximate Numerics - - -
float
Floating precision number data with the following valid values: -1.79E + 308
through -2.23E - 308, 0 and 2.23E + 308 through 1.79E + 308.
real
Floating precision number data with the following valid values: -3.40E + 38
through -1.18E - 38, 0 and 1.18E - 38 through 3.40E + 38.
Aleksandar Grbic
MCDBA, Senior Database Administrator
"Dimbit" wrote:
> Has anyone else noticed that SQL is handling Real numbers incorrectly, or am
> I wrong at this finding?
> The below query is an example. (you can try any number of decimal
> multiplications, or even sometimes decimal additions, but only seems to
> affect 'REAL' numbers - numeric, decimal, money and float seem to do just
> fine)
> SELECT convert(real,.11) * CONVERT(real,3)
> this returns 0.32999998 instead of .33.
> This might not be a problem inherent to SQL as I get simular problems
> handling real numbers from a Java based application, so I'm not sure if this
> problem might be Windows or Hardware based. I did this same test on several
> different machines, (dual Xeons and P4 laptops) and every time it returns the
> incorrect result.
I wrong at this finding?
The below query is an example. (you can try any number of decimal
multiplications, or even sometimes decimal additions, but only seems to
affect 'REAL' numbers - numeric, decimal, money and float seem to do just
fine)
SELECT convert(real,.11) * CONVERT(real,3)
this returns 0.32999998 instead of .33.
This might not be a problem inherent to SQL as I get simular problems
handling real numbers from a Java based application, so I'm not sure if this
problem might be Windows or Hardware based. I did this same test on several
different machines, (dual Xeons and P4 laptops) and every time it returns the
incorrect result.
This issue also applies to the float data type. Real and float data types
can only store approximate numeric data because some values cannot be stored
precisely. Use decimal or money when exact decimal values are required.
From the Books Online:
<Excerpt href="http://links.10026.com/?link=createdb.chm::/cm_8_des_04_82ic.htm">
Approximate numeric (floating-point) data consists of data preserved as
accurately as the binary numbering system can offer. Approximate numeric
data is stored using the float and real data types in SQL Server. For
example, because the fraction one-third in decimal notation is .333333
(repeating), this value cannot be represented precisely using approximate
decimal data. Therefore, the value retrieved from SQL Server may not be
exactly what was stored originally in the column. Additional examples of
numeric approximations are floating-point values ending in .3, .6, and .7.
</Excerpt>
Hope this helps.
Dan Guzman
SQL Server MVP
"Dimbit" <Dimbit@.discussions.microsoft.com> wrote in message
news:1A0E0F28-5244-4C16-80EB-FC9B08B4B8B0@.microsoft.com...
> Has anyone else noticed that SQL is handling Real numbers incorrectly, or
> am
> I wrong at this finding?
> The below query is an example. (you can try any number of decimal
> multiplications, or even sometimes decimal additions, but only seems to
> affect 'REAL' numbers - numeric, decimal, money and float seem to do just
> fine)
> SELECT convert(real,.11) * CONVERT(real,3)
> this returns 0.32999998 instead of .33.
> This might not be a problem inherent to SQL as I get simular problems
> handling real numbers from a Java based application, so I'm not sure if
> this
> problem might be Windows or Hardware based. I did this same test on
> several
> different machines, (dual Xeons and P4 laptops) and every time it returns
> the
> incorrect result.
|||use decimal or numeric
read "DATA TYPES" in BOL
decimal
Fixed precision and scale numeric data from -10^38 +1 through 10^38 –1.
numeric
Functionally equivalent to decimal.
- - - Approximate Numerics - - -
float
Floating precision number data with the following valid values: -1.79E + 308
through -2.23E - 308, 0 and 2.23E + 308 through 1.79E + 308.
real
Floating precision number data with the following valid values: -3.40E + 38
through -1.18E - 38, 0 and 1.18E - 38 through 3.40E + 38.
Aleksandar Grbic
MCDBA, Senior Database Administrator
"Dimbit" wrote:
> Has anyone else noticed that SQL is handling Real numbers incorrectly, or am
> I wrong at this finding?
> The below query is an example. (you can try any number of decimal
> multiplications, or even sometimes decimal additions, but only seems to
> affect 'REAL' numbers - numeric, decimal, money and float seem to do just
> fine)
> SELECT convert(real,.11) * CONVERT(real,3)
> this returns 0.32999998 instead of .33.
> This might not be a problem inherent to SQL as I get simular problems
> handling real numbers from a Java based application, so I'm not sure if this
> problem might be Windows or Hardware based. I did this same test on several
> different machines, (dual Xeons and P4 laptops) and every time it returns the
> incorrect result.
Incorrect Handling of Real Numbers
Has anyone else noticed that SQL is handling Real numbers incorrectly, or am
I wrong at this finding?
The below query is an example. (you can try any number of decimal
multiplications, or even sometimes decimal additions, but only seems to
affect 'REAL' numbers - numeric, decimal, money and float seem to do just
fine)
SELECT convert(real,.11) * CONVERT(real,3)
this returns 0.32999998 instead of .33.
This might not be a problem inherent to SQL as I get simular problems
handling real numbers from a Java based application, so I'm not sure if this
problem might be Windows or Hardware based. I did this same test on several
different machines, (dual Xeons and P4 laptops) and every time it returns the
incorrect result.This issue also applies to the float data type. Real and float data types
can only store approximate numeric data because some values cannot be stored
precisely. Use decimal or money when exact decimal values are required.
From the Books Online:
<Excerpt href="http://links.10026.com/?link=createdb.chm::/cm_8_des_04_82ic.htm">
Approximate numeric (floating-point) data consists of data preserved as
accurately as the binary numbering system can offer. Approximate numeric
data is stored using the float and real data types in SQL Server. For
example, because the fraction one-third in decimal notation is .333333
(repeating), this value cannot be represented precisely using approximate
decimal data. Therefore, the value retrieved from SQL Server may not be
exactly what was stored originally in the column. Additional examples of
numeric approximations are floating-point values ending in .3, .6, and .7.
</Excerpt>
Hope this helps.
Dan Guzman
SQL Server MVP
"Dimbit" <Dimbit@.discussions.microsoft.com> wrote in message
news:1A0E0F28-5244-4C16-80EB-FC9B08B4B8B0@.microsoft.com...
> Has anyone else noticed that SQL is handling Real numbers incorrectly, or
> am
> I wrong at this finding?
> The below query is an example. (you can try any number of decimal
> multiplications, or even sometimes decimal additions, but only seems to
> affect 'REAL' numbers - numeric, decimal, money and float seem to do just
> fine)
> SELECT convert(real,.11) * CONVERT(real,3)
> this returns 0.32999998 instead of .33.
> This might not be a problem inherent to SQL as I get simular problems
> handling real numbers from a Java based application, so I'm not sure if
> this
> problem might be Windows or Hardware based. I did this same test on
> several
> different machines, (dual Xeons and P4 laptops) and every time it returns
> the
> incorrect result.|||use decimal or numeric
read "DATA TYPES" in BOL
decimal
Fixed precision and scale numeric data from -10^38 +1 through 10^38 â'1.
numeric
Functionally equivalent to decimal.
- - - Approximate Numerics - - -
float
Floating precision number data with the following valid values: -1.79E + 308
through -2.23E - 308, 0 and 2.23E + 308 through 1.79E + 308.
real
Floating precision number data with the following valid values: -3.40E + 38
through -1.18E - 38, 0 and 1.18E - 38 through 3.40E + 38.
Aleksandar Grbic
MCDBA, Senior Database Administrator
"Dimbit" wrote:
> Has anyone else noticed that SQL is handling Real numbers incorrectly, or am
> I wrong at this finding?
> The below query is an example. (you can try any number of decimal
> multiplications, or even sometimes decimal additions, but only seems to
> affect 'REAL' numbers - numeric, decimal, money and float seem to do just
> fine)
> SELECT convert(real,.11) * CONVERT(real,3)
> this returns 0.32999998 instead of .33.
> This might not be a problem inherent to SQL as I get simular problems
> handling real numbers from a Java based application, so I'm not sure if this
> problem might be Windows or Hardware based. I did this same test on several
> different machines, (dual Xeons and P4 laptops) and every time it returns the
> incorrect result.
I wrong at this finding?
The below query is an example. (you can try any number of decimal
multiplications, or even sometimes decimal additions, but only seems to
affect 'REAL' numbers - numeric, decimal, money and float seem to do just
fine)
SELECT convert(real,.11) * CONVERT(real,3)
this returns 0.32999998 instead of .33.
This might not be a problem inherent to SQL as I get simular problems
handling real numbers from a Java based application, so I'm not sure if this
problem might be Windows or Hardware based. I did this same test on several
different machines, (dual Xeons and P4 laptops) and every time it returns the
incorrect result.This issue also applies to the float data type. Real and float data types
can only store approximate numeric data because some values cannot be stored
precisely. Use decimal or money when exact decimal values are required.
From the Books Online:
<Excerpt href="http://links.10026.com/?link=createdb.chm::/cm_8_des_04_82ic.htm">
Approximate numeric (floating-point) data consists of data preserved as
accurately as the binary numbering system can offer. Approximate numeric
data is stored using the float and real data types in SQL Server. For
example, because the fraction one-third in decimal notation is .333333
(repeating), this value cannot be represented precisely using approximate
decimal data. Therefore, the value retrieved from SQL Server may not be
exactly what was stored originally in the column. Additional examples of
numeric approximations are floating-point values ending in .3, .6, and .7.
</Excerpt>
Hope this helps.
Dan Guzman
SQL Server MVP
"Dimbit" <Dimbit@.discussions.microsoft.com> wrote in message
news:1A0E0F28-5244-4C16-80EB-FC9B08B4B8B0@.microsoft.com...
> Has anyone else noticed that SQL is handling Real numbers incorrectly, or
> am
> I wrong at this finding?
> The below query is an example. (you can try any number of decimal
> multiplications, or even sometimes decimal additions, but only seems to
> affect 'REAL' numbers - numeric, decimal, money and float seem to do just
> fine)
> SELECT convert(real,.11) * CONVERT(real,3)
> this returns 0.32999998 instead of .33.
> This might not be a problem inherent to SQL as I get simular problems
> handling real numbers from a Java based application, so I'm not sure if
> this
> problem might be Windows or Hardware based. I did this same test on
> several
> different machines, (dual Xeons and P4 laptops) and every time it returns
> the
> incorrect result.|||use decimal or numeric
read "DATA TYPES" in BOL
decimal
Fixed precision and scale numeric data from -10^38 +1 through 10^38 â'1.
numeric
Functionally equivalent to decimal.
- - - Approximate Numerics - - -
float
Floating precision number data with the following valid values: -1.79E + 308
through -2.23E - 308, 0 and 2.23E + 308 through 1.79E + 308.
real
Floating precision number data with the following valid values: -3.40E + 38
through -1.18E - 38, 0 and 1.18E - 38 through 3.40E + 38.
Aleksandar Grbic
MCDBA, Senior Database Administrator
"Dimbit" wrote:
> Has anyone else noticed that SQL is handling Real numbers incorrectly, or am
> I wrong at this finding?
> The below query is an example. (you can try any number of decimal
> multiplications, or even sometimes decimal additions, but only seems to
> affect 'REAL' numbers - numeric, decimal, money and float seem to do just
> fine)
> SELECT convert(real,.11) * CONVERT(real,3)
> this returns 0.32999998 instead of .33.
> This might not be a problem inherent to SQL as I get simular problems
> handling real numbers from a Java based application, so I'm not sure if this
> problem might be Windows or Hardware based. I did this same test on several
> different machines, (dual Xeons and P4 laptops) and every time it returns the
> incorrect result.
Incorrect group sum problem
Hi all,
I have encountered a very strange problem in RS2005. In my report I
show real estate transactions. When I have more than one record with
the same TR_ID (transaction id) I need to popolate nulls into certain
money fields like AGC_amt and some other fields. These amounts are the
same in all transactions with the same TR_ID and should be treated as
one value not a repeating value. Let's say $1,000 in AGC_amt should
remain $1,000 even for every transaction with the same TR_ID it
repeats. I conditionally place nulls into those texboxes that have the
same TR_ID in the previous record so only the first record has the
value.
=iif(Fields!TR_id.Value = Previous(Fields!TR_id.Value), nothing,
Fields!AGC_amt.Value)
I need to sum up all these values in my group footer. What I have in my
report is the amount equal to total as if I did not place nulls! It is
easy to show in example:
tr_id AGC_am
----
12345 $1,000
same tr_id I place null here although it is $1,000 in dataset
same tr_id also null for the same reason
group sum: $3,000 (instead of $1,000 what I expect!)
formula for the sum in group footer:
=Sum(Fields!AGC_amt.Value)
What is wrong here? I tried to place zeros in place of nulls and get
the same incorrect number, i.e. I have $1,000, $0 and $0 totaling to
$3,000 in group footer!!!
I am going insane!
Please, help!
Thanks,
StanNever mind, everyone! My mistake. There was another field in my dataset
I was supposed to use in my group totals. I just did not know about it.
Stan
suslikovich wrote:
> Hi all,
> I have encountered a very strange problem in RS2005. In my report I
> show real estate transactions. When I have more than one record with
> the same TR_ID (transaction id) I need to popolate nulls into certain
> money fields like AGC_amt and some other fields. These amounts are the
> same in all transactions with the same TR_ID and should be treated as
> one value not a repeating value. Let's say $1,000 in AGC_amt should
> remain $1,000 even for every transaction with the same TR_ID it
> repeats. I conditionally place nulls into those texboxes that have the
> same TR_ID in the previous record so only the first record has the
> value.
> =iif(Fields!TR_id.Value = Previous(Fields!TR_id.Value), nothing,
> Fields!AGC_amt.Value)
> I need to sum up all these values in my group footer. What I have in my
> report is the amount equal to total as if I did not place nulls! It is
> easy to show in example:
> tr_id AGC_am
> ----
> 12345 $1,000
> same tr_id I place null here although it is $1,000 in dataset
> same tr_id also null for the same reason
> group sum: $3,000 (instead of $1,000 what I expect!)
> formula for the sum in group footer:
> =Sum(Fields!AGC_amt.Value)
> What is wrong here? I tried to place zeros in place of nulls and get
> the same incorrect number, i.e. I have $1,000, $0 and $0 totaling to
> $3,000 in group footer!!!
> I am going insane!
> Please, help!
> Thanks,
> Stan
I have encountered a very strange problem in RS2005. In my report I
show real estate transactions. When I have more than one record with
the same TR_ID (transaction id) I need to popolate nulls into certain
money fields like AGC_amt and some other fields. These amounts are the
same in all transactions with the same TR_ID and should be treated as
one value not a repeating value. Let's say $1,000 in AGC_amt should
remain $1,000 even for every transaction with the same TR_ID it
repeats. I conditionally place nulls into those texboxes that have the
same TR_ID in the previous record so only the first record has the
value.
=iif(Fields!TR_id.Value = Previous(Fields!TR_id.Value), nothing,
Fields!AGC_amt.Value)
I need to sum up all these values in my group footer. What I have in my
report is the amount equal to total as if I did not place nulls! It is
easy to show in example:
tr_id AGC_am
----
12345 $1,000
same tr_id I place null here although it is $1,000 in dataset
same tr_id also null for the same reason
group sum: $3,000 (instead of $1,000 what I expect!)
formula for the sum in group footer:
=Sum(Fields!AGC_amt.Value)
What is wrong here? I tried to place zeros in place of nulls and get
the same incorrect number, i.e. I have $1,000, $0 and $0 totaling to
$3,000 in group footer!!!
I am going insane!
Please, help!
Thanks,
StanNever mind, everyone! My mistake. There was another field in my dataset
I was supposed to use in my group totals. I just did not know about it.
Stan
suslikovich wrote:
> Hi all,
> I have encountered a very strange problem in RS2005. In my report I
> show real estate transactions. When I have more than one record with
> the same TR_ID (transaction id) I need to popolate nulls into certain
> money fields like AGC_amt and some other fields. These amounts are the
> same in all transactions with the same TR_ID and should be treated as
> one value not a repeating value. Let's say $1,000 in AGC_amt should
> remain $1,000 even for every transaction with the same TR_ID it
> repeats. I conditionally place nulls into those texboxes that have the
> same TR_ID in the previous record so only the first record has the
> value.
> =iif(Fields!TR_id.Value = Previous(Fields!TR_id.Value), nothing,
> Fields!AGC_amt.Value)
> I need to sum up all these values in my group footer. What I have in my
> report is the amount equal to total as if I did not place nulls! It is
> easy to show in example:
> tr_id AGC_am
> ----
> 12345 $1,000
> same tr_id I place null here although it is $1,000 in dataset
> same tr_id also null for the same reason
> group sum: $3,000 (instead of $1,000 what I expect!)
> formula for the sum in group footer:
> =Sum(Fields!AGC_amt.Value)
> What is wrong here? I tried to place zeros in place of nulls and get
> the same incorrect number, i.e. I have $1,000, $0 and $0 totaling to
> $3,000 in group footer!!!
> I am going insane!
> Please, help!
> Thanks,
> Stan
Subscribe to:
Posts (Atom)