August 18, 2016 at 3:46 pm
The database is SqlServer 2008 R2. (Microsoft SQL Server 2008 R2 (SP3) - 10.50.6000.34 (X64) )
I have a query that compares 2 float values trying to find "logically bad data" ie. the values don't match the statuses or the types defined in the processing.
The 2 values look identical in the query results, so I'm confused why they show up.
So, I tryed multiplying the 2 values by number of non-integer values to see if I could get some decimal type results showing the difference/problem.
I tried multiplying these values with no differences ( being visibly unequal). Finally, when I multiplied the 2 values by 1.37001.. i got the results that were NOT equal.
the 2 values in the database column were 71.71.
The results are below, the ONLY data with a problem is the 71.71 data. The 2 other rows where "not equal".
both values when multiplied by 1.37001...
Value 1 Value 2
98.2434171 98.2434170999999
B APNO P APNO OrigValue1 OrigValue2 V1 x 1.37001 V2 x 1.37001 V1 x 1.00001 V2 x 1.00001 V1 x 1.001 V2 x 1.001 TRNNO TRNTYPE STAT TRNCAT CASHAMT CHECKAMT1 CREDITAMT APKEY ADDBY ADDDTTM AMT AMTCONF APKEY CASHAMT CHECKAMT1 CHECKAMT2 CHECKNO1 CHECKNO2 CNTCTKEY COMMENTS CONFDTTM CONTID CREDEXPDT CREDITACCT CREDITAMT CREDITNAME ESCROWNO MODBY MODDTTM REGTRANNO SRCBGTNO STAT TRNCAT TRNDTTM TRNEMP TRNNO TRNTYPE
--------- --------- ---------------------- ---------------------- ---------------------- ---------------------- ---------------------- ---------------------- ---------------------- ---------------------- ----------- ------- ---- ------ ---------------------- ---------------------- ---------------------- ----------- ------------------------------ ----------------------- ---------------------- ---------------------- ----------- ---------------------- ---------------------- ---------------------- ------------------------------ ------------------------------ ----------- --------------------------------------------------------------------------------------------------- ----------------------- ------ ----------------------- -------------------- ---------------------- ------------------------------ ---------- ------------------------------ ----------------------- ----------- ------------------------ ---- ------ ----------------------- ------------ ----------- -------
-- 1132 71.71 71.71 98.2434171 98.2434170999999 71.7107171 71.7107171 71.78171 71.78171 121440 P P C 0 71.71 0 37002 PDPST 2000-02-23 09:13:33.310 71.71 71.71 37002 0 71.71 0 3505 NULL 62061 NULL 2000-02-23 09:13:47.060 NULL NULL NULL 0 NULL NULL NULL NULL 1 CASH P C 2000-02-23 09:13:33.280 PDPST 121440 P
111636 -- 77972.7 77972.72 106823.378727 106823.4061272 77973.479727 77973.4997272 78050.6727 78050.69272 165851 P P C 77972.72 0 0 126433 PW WHOLESALE CONV 2004-03-25 13:37:24.747 77972.7 77972.72 126433 77972.72 0 0 NULL NULL 752002 10/05/2001 NULL NULL NULL NULL 0 NULL NULL NULL NULL 1 NULL P C 2001-10-05 13:37:25.000 CONV 165851 P
66649 -- 42 0 57.54042 0 42.00042 0 42.042 0 141630 P P C 0 42 0 58788 PDRLW 2003-10-14 10:51:29.000 42 0 58788 0 42 0 7175 NULL 600441 NULL 2003-10-15 09:16:23.000 NULL NULL NULL 0 NULL NULL NULL NULL 1 CASH P C 2003-10-14 10:51:29.000 PDRLW 141630 P
(3 row(s) affected)
August 18, 2016 at 4:13 pm
Floating point values are only approximate values rather than exact values. If you need to make an equals comparison, you need to use decimal values.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
August 18, 2016 at 4:20 pm
This is even in the Microsoft documentation.
Using decimal, float, and real Data
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.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
August 22, 2016 at 3:18 pm
drew.allen (8/18/2016)
This is even in the Microsoft documentation.Using decimal, float, and real Data
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.Drew
Which only proves how many mathematically illiterate people work for Microsoft.
_____________
Code for TallyGenerator
August 22, 2016 at 3:25 pm
The 2 values look identical in the query results,
Query results are shown in some form of decimal representation.
What you see on the screen might be not the actual numbers in the database.
Same as with datetime - you see values converted to strings. According to the conversion rules enforced by the computer settings.
Same datetime might look quite different on different screens.
Make sure you understand what you are comparing.
_____________
Code for TallyGenerator
August 22, 2016 at 4:28 pm
when i need to compare two floats, I tend to use this syntax
WHERE ABS(Float1 - Float2)<@Variance
So, for comparing to 2 d.p. I set @Variance = 0.01
to compare to 4 d.p. set @Variance = 0.0001
etc...
MM
select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);August 23, 2016 at 8:43 am
mister.magoo (8/22/2016)
when i need to compare two floats, I tend to use this syntaxWHERE ABS(Float1 - Float2)<@Variance
So, for comparing to 2 d.p. I set @Variance = 0.01
to compare to 4 d.p. set @Variance = 0.0001
etc...
The above is an excelent advise,
mister magoo's solution works with a fixed scale.
If the scale of the two floats is not known or can vary a lot, a 'relative' compare would be better. See the script below with some examples.
declare @a float = 0.0000383733
declare @b-2 float = 0.0000383743
-- declare @a float = 3837330000 -- Example two (has the same relative difference)
-- declare @b-2 float = 3837430000
print 1.0+(@a-@b)/@b -- Visual fast representation to see the relative difference.
IF ABS((@a-@b)/@b) < 0.001 PRINT 'Less' ELSE PRINT 'More'
IF ABS((@a-@b)/@b) < 0.0001 PRINT 'Less' ELSE PRINT 'More'
IF ABS((@a-@b)/@b) < 0.00001 PRINT 'Less' ELSE PRINT 'More'
IF ABS((@a-@b)/@b) < 0.000001 PRINT 'Less' ELSE PRINT 'More'
Ben
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply