values returning as -1.(random numerics)E-13

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

values returning as -1.(random numerics)E-13

Author

Topic

SqlGirl87
Starting Member

26 Posts

Posted - 2012-09-06 : 11:53:32

Hi All,

I was wondering if anyone can explain why this happens please?

I was trying to write an a query to check the data integrity of a table, basically the 'OUTS' should be 0 IF field.VALUE = field.PAID

I then wanted to checked that OUTS wasn't 0 IF value <> paid as this would be wrong so I wrote


select invoice, * from table where value <> paid and outs ='0 but it returned lines with the below exact figures

INVOICE   VALUE        PAID        OUTS
INV1      913.66       913.66      0

These are the exact values, no rounding etc put on them so that invoice should not return.

I then run a


select VALUE - PAID AS 'TEST' from table (nolock) where
invoice = 'INV1'
and it returned

TEST
-1.13686837721616E-13

I'm wondering if anyone knows how/why this happens - how to correct, if it is something that needs correcting? all advise welcome but in the simplest terms possible please :)

Thanks!

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2012-09-06 : 11:56:24

If someone is able to help then at least the data types of the columns are needed.

Too old to Rock'n'Roll too young to die.

SqlGirl87
Starting Member

26 Posts

Posted - 2012-09-06 : 12:11:13

quote:
Originally posted by webfred

If someone is able to help then at least the data types of the columns are needed.

Too old to Rock'n'Roll too young to die.

I meant to add that sorry, also that it works for the majority of the data in there, it's only for a handful of invoices in that table where it doesn't - They are all set as float

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2012-09-06 : 12:15:00

http://msdn.microsoft.com/en-us/library/ms187912(v=sql.105).aspx

Because of the approximate nature of the float and real data types, do not use these data types when exact numeric behavior is required, such as in financial applications, in operations involving rounding, or in equality checks. Instead, use the integer, decimal, money, or smallmoney data types.

Avoid using float or real columns in WHERE clause search conditions, especially the = and <> operators. It is best to limit float and real columns to > or < comparisons.

Too old to Rock'n'Roll too young to die.

SqlGirl87
Starting Member

26 Posts

Posted - 2012-09-07 : 03:50:58

quote:
Originally posted by webfred

http://msdn.microsoft.com/en-us/library/ms187912(v=sql.105).aspx

Because of the approximate nature of the float and real data types, do not use these data types when exact numeric behavior is required, such as in financial applications, in operations involving rounding, or in equality checks. Instead, use the integer, decimal, money, or smallmoney data types.

Avoid using float or real columns in WHERE clause search conditions, especially the = and <> operators. It is best to limit float and real columns to > or < comparisons.

Too old to Rock'n'Roll too young to die.

Sadly I can't change that, but thanks for the info; good to know!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources