Help with a float value

Question

Post reply

Help with a float value

Harold Buckner

SSCarpal Tunnel

Points: 4175
More actions
February 14, 2012 at 2:00 pm

#268424

Our company has a vender package that has a table that contains a float data type. The system populates the value within the code. Today we found out a value that is stored is not what we see in the table.
So, I am confused on how I can see one thing and what is stored is something different.
Here is an example.
SELECT
org -- Float (53)
FROM
Table
Results.
org
----------------------
200100100000000
(1 row(s) affected)
You see 200100100000000 as the result
but add the result to the where clause and it returns nothing.
SELECT
org
FROM
TABLE
WHERE
org = 200100100000000
Results
org
----------------------
(0 row(s) affected)
If I cast(org as real)
SELECT
org
FROM
TABLE
WHERE
CAST( org AS REAL) = 200100100000000
Result
org
----------------------
200100100000000
(1 row(s) affected)
So I'm having a hard time grasping why the data stored is not really what I am seeing. The precision is 15 digits and the value takes up all of it, so there is no rounding that is taking place.
Can someone help me understand why or how this happens.
Thanks

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

LutzM SSC Guru Points: 107049 More actions · Answer 1

Hmm... When running the following code the correct result will be returned in both cases.

can you post a similar sample code with a value that would allow to duplicate the scenario you're struggling with?

DECLARE @tbl TABLE

(org FLOAT(53))

INSERT INTO @tbl

select 200100100000000

SELECT org

FROM @tbl

SELECT org

FROM @tbl

WHERE org = 200100100000000

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

Harold Buckner SSCarpal Tunnel Points: 4175 More actions · Answer 2

Thanks for trying to help me out with this.

This is sort of the problem. The value ( which looks like 200100100000000) got inserted into the table by the application and what we see as the value does not seem to be the actual true value or data. Therefore if I try to select based upon what I see in the table, no results are returned.

For instance

If I select two of my rows and both having a value of 200100100000000 but the first row has the issue I am referring to. I try a series of different cast statements and converting them to Varchar, real and varbinary,

Notice the Varbinary is the only different value.

results:

org vchar real varbinary

---------------------- ------------------------------ ------------- --------------------------------------------------------------

200100100000000 2.001e+014 2.001001E+14 0x42E6BFADEC291FFF

200100100000000 2.001e+014 2.001001E+14 0x42E6BFADEC292000

But like I was saying if I try to just select org from table where org = 200100100000000 I would only get one row the row with the varbinary of 0x42E6BFADEC292000.

So, the application somehow inserted something in the column that is not truly 200100100000000 but I'm having trouble understanding what I'm seeing is not really the value.

IF I select org from table where CAST( org AS REAL ) = 200100100000000

I would get two rows.

Hope this helps. But I don't know how to even mimic the issue.

Harold Buckner SSCarpal Tunnel Points: 4175 More actions · Answer 3

UPDATE:

I have now found what is the true value stored in the table by using the STR() function.

SELECT

org,

STR ( org, 30,30)

FROM

TABLE

WHERE

CAST( org AS REAL ) = 200100100000000

org

---------------------- ------------------------------

200100100000000 200100099999999.97000000000000

(1 row(s) affected)

SELECT

org

FROM

TABLE

WHERE

org = 200100099999999.97000000000000

org

----------------------

200100100000000

(1 row(s) affected)

So now I need to find out why the application inserts a value like that rather then the whole number of 200100100000000 like it should be.

Thanks

Uripedes Pants Hall of Fame Points: 3701 More actions · Answer 4

I've always been told not to try to make an equality comparison on float values because they are "appoximate".

from Microsoft:

http://msdn.microsoft.com/en-us/library/ms173773.aspx

on Embarcodero:

http://edn.embarcadero.com/article/22507

Phil Parkin SSC Guru Points: 246960 More actions · Answer 5

I have now found what is the true value stored in the table by using the STR() function.

You should not expect the STR() function to throw any light on this problem; it is more likely that it will just add to your confusion.

That's because the STR() function behaves as though it does an initial CAST to FLOAT and then back to whatever NUMERIC scale/precision you have specified, introducing errors along the way.

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

Harold Buckner SSCarpal Tunnel Points: 4175 More actions · Answer 6

Phil Parkin (2/16/2012)
I have now found what is the true value stored in the table by using the STR() function.
You should not expect the STR() function to throw any light on this problem; it is more likely that it will just add to your confusion.
That's because the STR() function behaves as though it does an initial CAST to FLOAT and then back to whatever NUMERIC scale/precision you have specified, introducing errors along the way.

I understand. I was just trying to find what the actual value was that the application inserted. This provided it to me.