Oracle Sql Divide By Zero Error
Contents |
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 oracle nullif Stack Overflow the company Business Learn more about hiring developers or posting ads
Divide By Zero Error Encountered Excel
with us Stack Overflow Questions Jobs Documentation Tags Users Badges Ask Question x Dismiss Join the Stack Overflow Community Stack Overflow
Sql Nullif
is a community of 6.2 million programmers, just like you, helping each other. Join them; it only takes a minute: Sign up How to avoid the “divide by zero” error in SQL? up vote 188
Sql Nullif 0
down vote favorite 47 I have this error message: Msg 8134, Level 16, State 1, Line 1 Divide by zero error encountered. What is the best way to write SQL code so that I will never see this error message again? I could do either of the following: Add a where clause so that my divisor is never zero Or I could add a case statement, so that there is msg 8134 level 16 state 1 line 1 divide by zero error encountered a special treatment for zero. Is the best way to use a NullIf clause? Is there better way, or how can this be enforced? sql sql-server sql-server-2005 sql-server-2008 share|improve this question edited Jan 6 at 19:50 J.D. 4311525 asked May 14 '09 at 6:06 Henrik Staun Poulsen 4,93331220 4 Perhaps some data validation is in order. –Anthony May 14 '09 at 19:17 add a comment| 15 Answers 15 active oldest votes up vote 353 down vote accepted In order to avoid a "Division by zero" error we have programmed it like this: Select Case when divisor=0 then null Else dividend / divisor End ,,, But here is a much nicer way of doing it: Select dividend / nullif(divisor, 0) ... Now the only problem is to remember the NullIf bit, if I use the "/" key. share|improve this answer edited Dec 20 '12 at 1:04 Community♦ 11 answered May 14 '09 at 6:10 Henrik Staun Poulsen 4,93331220 that's the way I would have solved it. –J. Polfer May 14 '09 at 19:21 4 A much nicer Way of doing it "Select dividend / nullif(divisor, 0) ..." breaks if divisor is NULL. –Anderson Dec 1 '14 at 10:51 add a comment| up vote
By Chris FehilyJul 25, 2008 Topics: ProductivitySuppose you divide by zero error encountered in stored procedure want to calculate the male–female ratios for various school redshift nullif clubs, but you discover that the following query fails and issues a divide-by-zero error when vertica nullif it tries to calculate ratio for the Lord of the Rings Club, which has no women: SELECT club_id, males, females, males/females AS ratio http://stackoverflow.com/questions/861778/how-to-avoid-the-divide-by-zero-error-in-sql FROM school_clubs; You can use the function NULLIF to avoid division by zero. NULLIF compares two expressions and returns null if they are equal or the first expression otherwise. Rewrite the query as: SELECT club_id, males, females, males/NULLIF(females, 0) AS ratio FROM school_clubs; Any number divided by http://www.peachpit.com/blogs/blog.aspx?uk=Avoiding-division-by-zero-with-NULLIF-Five-SQL-Tips-in-Five-Days-Part-5- NULL gives NULL, and no error is generated. Be sure to look back over my posts this week for more SQL tips. And for more on SQL, be sure to check out my book SQL: Visual QuickStart Guide, 3rd Edition. Share|Share on twitter Share on facebook Share on google Share on email✉ Other Things You Might LikeData at Work: Best practices for creating effective charts and information graphics in Microsoft Excel By Jorge CamõesBook $35.99Speaker Camp Workshop: Learn by Video By Russ Unger, Samantha StarmerDVD $39.99Windows 8: Visual QuickStart Guide By Barrie SosinskyBook $27.99 View Your Cart Account Sign InSearchSearch View Your Cart 👤 Account Sign In Create AccountTopicsAdobe PhotoshopAppleDesignDigital PhotographyDigital VideoGadgetsProductivity Web Design & Development StoreBookseBooksVideoWeb EditionsAuthorsSafariExploreAbout UsAcademic ResourcesAffiliatesArticlesBlogsNewslettersPressUser GroupsAboutAffiliatesContact UsJobsLegal NoticePrivacy PolicyPressPromotionsSite MapWrite for Us © 2016 Pearson Education, Peachpit. All rights reserved. 1301 Sansome Street, San Francisco, CA 94111
Library http://psoug.org/definition/ZERO_DIVIDE.htm mySQL Code Library PHP Code Library JavaScript Code Library Oracle Terms & Definitions Oracle Error Codes PSOUG Community Blogs Oracle Jobs Board PSOUG Forum Oracle User Group Directory Free Oracle Magazines Online Learning Center PSOUG Presentations Advanced Code Search News and divide by Events Sponsors Page Submit Code Contact Us Looking for the original pages? (formerly called "Morgan's Library") You can find them here. Term: ZERO_DIVIDE Definition: The ZERO_DIVIDE Exception (ORA-01476) occurs when a program attempts to divide a divide by zero number by zero. Example Usage: In this example, if the SELECT INTO statement raises a ZERO_DIVIDE exception, the local handler catches it and sets sal_calc to 3000. Execution of the handler is complete, so the sub-block terminates and execution continues with the INSERT statement. DECLARE sal_calc NUMBER(8,2); BEGIN INSERT INTO employees_table VALUES(303, 3000, 0); BEGIN SELECT salary / commission_pct INTO sal_calc FROM employees_table WHERE employee_id = 301; EXCEPTION WHEN ZERO_DIVIDE THEN sal_calc := 3000; END; INSERT INTO employees_table VALUES(304, sal_calc/100, .1); EXCEPTION WHEN ZERO_DIVIDE THEN NULL; END; / Home : Code Library : Sponsors : Privacy : Terms of Use : Contact Us 83 users online © 2009 psoug.org PSOUG LOGIN Username: Password: Forgot your password?