Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » ElevateDB Technical Support » Support Forums » ElevateDB General » View Thread |
Messages 1 to 4 of 4 total |
Annoying 429.341999999 in sum ... Windows bug? |
Mon, Dec 12 2011 1:45 AM | Permanent Link |
Adam Brett Orixa Systems | I have a column full of floats. All entered to 3 decimals or less (i.e. 1.234, 0.543 120.4).
I have run UPDATE TABLE xxx SET MyColumn = ROUND(MyColumn TO 3) When I view the column in a grid (and EDBMgr) I see the 3 decimals. When I run SUM(MyColumn) the total displays as 429.34199999 ... for just 1 group of records ... implying that there is a random record somewhere which is not exactly a whole number. -- I have heard vaguely about this behaviour generally in Windows, i.e. the inability for Windows to be precisely accurate with some FLOAT values ... but I have never seen it in EDB before. The data has been imported from Excel via a CSV file ... but crucially the CSV only shows accurate numbers ... so I am really at a loss as to where the inaccuracy is creeping in! -- This is a totally non-critical issue! My users will survive ... but I just thought I would post it to see what could be done about it. Adam |
Mon, Dec 12 2011 4:26 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | Adam
What type do you have the column defined as? If its FLOAT then Approximate numeric types are used when you wish to store a numeric value in an approximate representation with a floating decimal point. Using approximate numeric types can cause rounding errors due to the fact that certain numbers such as 0.33 cannot be accurately represented using floating-point precision. try DECIMAL or NUMERIC Exact numeric types are used when you wish to store a numeric value in its exact representation without accumulating rounding errors. Specifically, NUMERIC and DECIMAL types allow you to specify the scale so that any numeric values with a greater scale are automatically rounded to the specified scale using the bankers rounding algorithm, which simply says that any digits past the specified scale are rounded to the specified scale using the following logic: Roy Lambert [Team Elevate] |
Tue, Dec 13 2011 5:07 AM | Permanent Link |
Adam Brett Orixa Systems | Thanks very much Roy, I will redefine columns with different data-types when I need precision.
I am still confused why the inaccuracy persists even after I have run UPDATE mytable SET Mycolumn = ROUND(MyColumn to 3) ... I would have assumed that would remove the error. |
Tue, Dec 13 2011 6:26 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | Adam
>I am still confused why the inaccuracy persists even after I have run > >UPDATE mytable >SET Mycolumn = ROUND(MyColumn to 3) > >.. I would have assumed that would remove the error. Its a logically good assumption, unfortunately its wrong. The problem (as I understand it) stems from the way in which floating point numbers are stored and manipulated. You've told it to round (say) 22 / 7 to three decimals ie 3.142 but that's not necessarily how its stored it might be 3.141999999. Any arithmetical operation (probably any mathematical operation as well) works on the underlying data as stored. Its why comparisons on floating point numbers are generally best carried out with a range equating to approximately. I think Delphi actually has some dedicated comparison operators for this - not sure cos I don't use them. Roy Lambert [Team Elevate] |
This web page was last updated on Monday, April 29, 2024 at 05:23 AM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |