Oracle Rounding Error
Contents |
by David Goldberg, published in the March, 1991 issue of Computing Surveys. Copyright 1991, Association for Computing Machinery, Inc., reprinted by permission. Abstract Floating-point arithmetic is considered an esoteric subject floating point precision error by many people. This is rather surprising because floating-point is ubiquitous in
Floating Point Rounding Error
computer systems. Almost every language has a floating-point datatype; computers from PCs to supercomputers have floating-point accelerators; most compilers
Floating Point Rounding Error Example
will be called upon to compile floating-point algorithms from time to time; and virtually every operating system must respond to floating-point exceptions such as overflow. This paper presents a tutorial on those
Floating Point Arithmetic Error
aspects of floating-point that have a direct impact on designers of computer systems. It begins with background on floating-point representation and rounding error, continues with a discussion of the IEEE floating-point standard, and concludes with numerous examples of how computer builders can better support floating-point. Categories and Subject Descriptors: (Primary) C.0 [Computer Systems Organization]: General -- instruction set design; D.3.4 [Programming Languages]: Processors floating point ieee -- compilers, optimization; G.1.0 [Numerical Analysis]: General -- computer arithmetic, error analysis, numerical algorithms (Secondary) D.2.1 [Software Engineering]: Requirements/Specifications -- languages; D.3.4 Programming Languages]: Formal Definitions and Theory -- semantics; D.4.1 Operating Systems]: Process Management -- synchronization. General Terms: Algorithms, Design, Languages Additional Key Words and Phrases: Denormalized number, exception, floating-point, floating-point standard, gradual underflow, guard digit, NaN, overflow, relative error, rounding error, rounding mode, ulp, underflow. Introduction Builders of computer systems often need information about floating-point arithmetic. There are, however, remarkably few sources of detailed information about it. One of the few books on the subject, Floating-Point Computation by Pat Sterbenz, is long out of print. This paper is a tutorial on those aspects of floating-point arithmetic (floating-point hereafter) that have a direct connection to systems building. It consists of three loosely connected parts. The first section, Rounding Error, discusses the implications of using different rounding strategies for the basic operations of addition, subtraction, multiplication and division. It also contains background information on the two methods of measuring rounding error, ulps and relative error. The second part discusses the IEEE floating-point standard, which is becoming
here for a quick overview of the site Help Center Detailed answers to any questions you might have Meta Discuss the workings and policies of this site About Us Learn more about Stack Overflow the company Business Learn more about hiring developers floating point python or posting ads with us Stack Overflow Questions Jobs Documentation Tags Users Badges Ask Question floating point numbers explained x Dismiss Join the Stack Overflow Community Stack Overflow is a community of 6.2 million programmers, just like you, helping each other. Join them; floating point arithmetic examples it only takes a minute: Sign up Oracle ROUND function returns wrong value up vote 2 down vote favorite I run this sql statement on Oracle 11g database select round(79/3 + 87/3 + 86/6 + 95/6) from dual and it https://docs.oracle.com/cd/E19957-01/806-3568/ncg_goldberg.html returns 85 select round(79/3 + 87/3 + 86/6 + 95/6,1) from dual returns 85.5 select round(85.5) from dual returns a correct value 86 Does anyone know why the first SQL statement doesn't return a correct value 86 but it rounds it down to 85 ?? sql oracle oracle11g share|improve this question asked Jul 9 '12 at 15:29 mefist 1313 1 per the docs, it will round to the number of places to right of decimal you specify, default being http://stackoverflow.com/questions/11398260/oracle-round-function-returns-wrong-value 0. So Oracle isn't wrong, you just didn't read the docs. So the "correct" value depends on what you tell Oracle to do. –tbone Jul 9 '12 at 17:34 add a comment| 3 Answers 3 active oldest votes up vote 10 down vote accepted If you do: select 79/3 + 87/3 + 86/6 + 95/6 from dual; Oracle will return 85.49999999999999999999999999999999999999, which when rounded to 0 decimal places is indeed 85. It's an artifact of floating point arithmetic. share|improve this answer answered Jul 9 '12 at 15:37 Xophmeister 5,1061737 See docs.oracle.com/cd/E19957-01/806-3568/ncg_goldberg.html for details –Xophmeister Jul 9 '12 at 15:39 thanks, that was very helpful. –mefist Jul 9 '12 at 15:53 Any idea how to make ROUND function to round this correctly? I'm thinking about something like that select round((79/3 + 87/3 + 86/6 + 95/6)+0.000000001) from dual BTW I'm only interested in zero precision –mefist Jul 9 '12 at 16:01 Round it twice: select round(round(79/3 + 87/3 + 86/6 + 95/6, 1)) from dual Although, off the top of my head, this isn't guaranteed to work in general cases. –Xophmeister Jul 9 '12 at 16:07 add a comment| up vote 1 down vote 79/3 + 87/3 + 86/6 + 95/6 will return floating point arithmetic which doesn't have the rounding behavior of IEEE 754. See the difference between Number and Float here To get the correct result back you
Technology and Trends Enterprise Architecture and EAI ERP Hardware IT Management and Strategy Java Knowledge Management Linux Networking http://oracle.ittoolbox.com/groups/technical-functional/oracle-apps-l/rounding-error-in-ar-oracle-apps-3417025 Oracle PeopleSoft Project and Portfolio Management SAP SCM Security Siebel Storage https://www.pythian.com/blog/rounding-errors-in-mysql-with-decimal-type/ UNIX Visual Basic Web Design and Development Windows < Back CHOOSE A DISCUSSION GROUP Research Directory TOPICS Database Hardware Networking SAP Security Web Design MEMBERS Paul_Pedant DACREE MarkDeVries VoIP_News Inside-ERP MacProTX Inside-CRM I_am_the_dragon maxwellarnold Michael Meyers-Jouan TerryCurran Chris_Day Andrew.S.Baker Ramnath.Awate JoeTorre Craig Borysowich floating point Locutus Dennis Stevenson DukeGanote Richard iudithm mircea_luca Clinton Jones bracke Nikki Klein AbhaiTripathi Iqbalyk Adrian_Grigoriu bluesguyAZ59 numbersguyPA COMPANIES EdgeWave Sophos Pivotal CRM Wave Direct View All Topics View All Members View All Companies Toolbox for IT Topics Oracle Groups Ask a New Question Oracle Applications Your premier resource for objective technical discussion and peer-to-peer support floating point rounding on Oracle Applications. Home | Invite Peers | More Oracle Groups Your account is ready. You're now being signed in. Solve problems - It's Free Create your account in seconds E-mail address is taken If this is your account,sign in here Email address Username Between 5 and 30 characters. No spaces please The Profile Name is already in use Password Notify me of new activity in this group: Real Time Daily Never Keep me informed of the latest: White Papers Newsletter Jobs By clicking "Join Now", you agree to Toolbox for Technology terms of use, and have read and understand our privacy policy. Rounding error in AR Oracle Apps Iwan Jusuf asked Apr 5, 2010 | Replies (3) Hi experts, Has anyone ever face a rounding problem in AR invoice? I found a problem where the invoice was not rounded as usual pattern. Example, if the invoice amount HKD 1,433,640,77 and the cross rate is 7.753 into USD, the functional curren
ITProfessional ServicesIT Strategy + Consulting ServicesImplementation ServicesManaged ServicesTechnology PracticesAdvanced AnalyticsBig DataCloudDatabasesDevOpsInfrastructure ManagementTechnologiesAmazon Web ServicesMicrosoft AzureGoogle Cloud PlatformCassandraHadoopMySQLOracleOracle EBSMicrosoft SQL ServerResourcesOverviewWhite PaperseBooksWebinarsData SheetsArticlesPresentationsVideosClientsAboutLeadership TeamBoard of DirectorsExpertsCareersPartnersSocial ResponsibilityPythia IndexNews & EventsWorldwide OfficesContactBlog CONTACT US Your source for technical trends, tips, and best practices from Pythian experts Subscribe // non-idevice version of form ?> // idevice version of form ?> Privacy Policy Business Insights Technical Track Pythian Life Rounding Errors in MYSQL with DECIMAL Type by Sandi BarrSeptember 28, 2011 Posted in: Technical Track Tags: Group Blog Posts, MySQL, Technical Blog It seems to be assumed that fixed-point values, DECIMAL and NUMERIC, in MySQL are not susceptible to rounding errors because they are exact numeric data types. It must be kept it mind, that there are limits in precision, and the maximum number of digits for DECIMAL is 65. Additionally, a DECIMAL column can be assigned a precision or scale that could have the potential affect of truncation to the allowed number of digits. What I am discussing here is rounding error.Take the salary data from the example employees database. Run two queries against the same data, one taking a SUM of the data in a subquery, another using GROUP BY WITH ROLLUP to calculate the same SUM.mysql> select sum(hourly) from ( -> select emp_no,year(from_date), -> sum(salary/(52*40)) as hourly -> from salaries-> group by emp_no,year(from_date) -> ) t; +---------------+ | sum(hourly) | +---------------+ | 87250372.2511 | +---------------+ 1 row in set (47.34 sec) mysql> select emp_no,year(from_date),sum(salary/(52*40)) -> from salaries -> group by emp_no,year(from_date) with rollup; +--------+-----------------+---------------------+ | emp_no | year(from_date) | sum(salary/(52*40)) | +--------+-----------------+---------------------+ ... | 240754 | 1994 | 22.6274 | | 240754 | 1995 | 23.9077 | | 240754 | 1996 | 24.3221 | | 240754 | 1997 | 26.2399 | ... | 499998 | 1999 | 24.0245 | | 499998 | 2000 | 24.6067 | | 499998 | 2001 | 26.4438 | | 499998 | NULL | 201.9183 | |