Handling Divide By Zero Error In Sql Server
Contents |
By Chris FehilyJul 25, 2008 Topics: ProductivitySuppose you oracle sql divide by zero want to calculate the male–female ratios for various school
Divide By Zero Error Encountered Excel
clubs, but you discover that the following query fails and issues a divide-by-zero error when msg 8134 level 16 state 1 line 1 divide by zero error encountered 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
Nullif 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 divide by zero error encountered in stored procedure 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
Dan Short (@danshort) Using NULLIF() To Prevent Divide-By-Zero Errors In SQL By Ben Nadel on October 3, 2007 Tags: SQL Boyan Kostadinov just sent me a cool link to an article that is the final part in a
Divide By Zero Error Encountered. The Statement Has Been Terminated
four part series that discusses the SQL NULL value. I haven't read the first three
Divide By Zero Error Encountered C#
parts yet, but there is a really cool tip in the fourth part on using NULLIF() to prevent divide-by-zero errors in a SQL divide by 0 in 128 bit arithmetic netezza call.The idea here is that, as with any other form of math that I know of, you cannot divide by zero in a SQL call. Therefore, running this code:
SERVER - How to Fix Error 8134 Divide by Zero Error Encountered August 27, 2016Pinal DaveSQL Tips and Tricks5 commentsHere is one of the most popular questions: How to overcome (Error 8134) Divide http://blog.sqlauthority.com/2016/08/27/sql-server-fix-error-8134-divide-zero-error-encountered/ by Zero Error Encountered in SQL Server?Before we see the answer of this question, let us see how to recreate this error.Run following script in SQL Server Management Studio http://www.itjungle.com/fhg/fhg051210-story02.html window.DECLARE @Var1 FLOAT; DECLARE @Var2 FLOAT; SET @Var1 = 1; SET @Var2 = 0; SELECT @Var1/@Var2 MyValue;When you execute above script you will see that it will throw error divide by 8134. Msg 8134, Level 16, State 1, Line 6 Divide by zero error encountered. Here is the screenshot of the error.Now there are multiple ways to avoid this error to happen. We will see two of the most popular methods to overcome this error.Method 1: Use NullIf FunctionHere is the SQL script with NullIf FunctionDECLARE @Var1 FLOAT; DECLARE divide by zero @Var2 FLOAT; SET @Var1 = 1; SET @Var2 = 0; SELECT @Var1/NULLIF(@Var2,0) MyValue;When you use the NULLIF function, it converts the zero value to Null and leading to the entire result set to be NULL instead of an error.Method 2: Use Case StatementHere is the SQL script with CASE StatementDECLARE @Var1 FLOAT; DECLARE @Var2 FLOAT; SET @Var1 = 1; SET @Var2 = 0; SELECT CASE WHEN @Var2 = 0 THEN NULL ELSE @Var1/@Var2 END MyValue;When you use CASE statement, it converts the zero value to Null and leading to the entire result set to be NULL instead of an error.Let me know if you have any other alternate solution. I will be happy to publish in the blog with due credit.Reference: Pinal Dave (http://blog.sqlauthority.com) Tags: SQL Error Messages, SQL Server340Related Articles Developer Training Courses - Online Courses to Learn SQL Server, MySQL and Technology July 3, 2013Pinal Dave SQL SERVER - Shrinking Database NDF and MDF Files - Readers’ Opinion June 15, 2010Pinal Dave SQL SERVER - Fixing Backup Error - Operating sy
8 knows that division by zero is a no-no, or at least they should. Everybody who's used SQL for any amount of time knows that we can use a CASE expression to prevent division by zero. But do you know the other way to prevent division by zero in SQL? Assume a database file called SomeTable, with fields called Quantity and Amount. Let's divide Amount by Quantity. First, here's the usual case statement. select itnbr, Amount, Quantity, case when Quantity <> 0 then Amount / Quantity else 0 end from SomeTable If Quantity is not zero, the division takes place. If Quantity is zero, the calculation yields a zero. We could just as easily return any other numeric value, the value one, for example. select itnbr, Amount, Quantity, case when Quantity <> 0 then Amount / Quantity else 1 end from SomeTable The other way to prevent division by zero is to use the NULLIF function. NULLIF requires two arguments. If the arguments are equal, NULLIF returns a null value. If they are not equal, NULLIF returns the first value. Here's the SQL. select itnbr, Amount, Quantity, Amount / nullif(Quantity,0) from SomeTable If Quantity is not zero, the two arguments do not match, and the resulting calculation is Amount divided by Quantity. But if Quantity is zero, the arguments do match, and the calculation is Amount divided by null, which yields a null value. If you use this technique, be sure to put the zero in the second argument of NULLIF. Post this story to del.icio.us Post this story to Digg Post this story to Slashdot Sponsored By SEQUEL SOFTWARE SEQUEL ViewPoint--Data Access & Analysisfor Power Systems Servers Easy to use by IT and end users Real-time data access and analysis Queries, reports, pivot tables, drill-down analysis, kpi dashboards IBM i-centric with multiplatform data access Expert support and training Secure data access GUI, Web & green screen inte