How To Fix Divide By Zero Error In Sql Server
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 Stack Overflow the company Business Learn more about hiring developers or posting ads
Divide By Zero Error Encountered. In Sql Server 2008
with us Stack Overflow Questions Jobs Documentation Tags Users Badges Ask Question x Dismiss Join the Stack divide by zero error encountered excel Overflow Community Stack Overflow is a community of 4.7 million programmers, just like you, helping each other. Join them; it only takes a minute: Sign msg 8134 level 16 state 1 line 1 divide by zero error encountered up Simple way to prevent a Divide By Zero error in SQL up vote 9 down vote favorite 2 I have a SQL query which used to cause a Divide By Zero exception, I've wrapped it in a CASE statement to
Oracle Sql Divide By Zero
stop this from happening. Is there a simpler way of doing this? Here's my code: Percentage = CASE WHEN AttTotal <> 0 THEN (ClubTotal/AttTotal) * 100 ELSE 0 END sql sql-server-2008 tsql sql-server-2005 share|improve this question asked Oct 28 '13 at 9:22 Denys Wessels 10.5k43872 1 It depends on how you wan't your query and result to behave when AttTotal is 0 or NULL, so there is no general way of doing it. I'd say your query is just fine. Even if you
Divide By Zero Error Encountered In Stored Procedure
write your query differently using ISNULL or NULLIF at the end it will likely execute the same. –Nenad Zivkovic Oct 28 '13 at 9:34 As has been pointed out by others, the logic in this CASE statement doesn't quite make sense to us. See my comment on my answer for a brief explanation and then choose whether you want your original answer or mine. My answer would be a more usual solution. –Tom Chantler Oct 28 '13 at 9:40 1 Possible duplicate of How to avoid the "divide by zero" error in SQL? –Henrik Staun Poulsen Mar 10 at 21:06 add a comment| 5 Answers 5 active oldest votes up vote 27 down vote accepted A nicer way of doing this is to use NULLIF like this: Percentage = 100 * ClubTotal / NULLIF(AttTotal, 0) share|improve this answer answered Oct 28 '13 at 9:25 Tom Chantler 11.3k42940 2 This will return NULL if AttTotal = 0, while the query in question returns 0. –Nenad Zivkovic Oct 28 '13 at 9:31 3 @NenadZivkovic - Returning NULL for a divide by zero error probably makes more mathematical sense anyway though. –Martin Smith Oct 28 '13 at 9:34 I agree with @Martin Smith. If you have a situation where the ClubTotal is a number and AttTotal is zero, it doesn't make sense to return zero. Or any other number :-) Returning zero implies ClubTotal is zero and AttTotal is more than zero. –Tom Chant
OK, some of the data I am using isnt playing nicely and once in a while I get Divide by zero error encountered. Divide by zero error encountered. Msg 8134, Level 16, State 1, Line 2 To avoid this I am using a CASE as: DECLARE @Int1 AS INT , @Int2 AS INT SET @Int1 = 6 nullif sql SET @Int2 = 2 SELECT @Int1 / @Int2 SET @Int1 = 6 SET @Int2 = 0 SELECT @Int1
Divide By Zero Error Encountered. The Statement Has Been Terminated
/ CASE WHEN @Int2 = 0 THEN 1 ELSE @Int2 END is there a better way of getting the same results but without littering by nice tidy divide by 0 in 128 bit arithmetic netezza SELECT statements with hundreds of CASE WHEN ... THEN ... ELSE ... END's?? Am I missing some super useful function that does this in one line? [edit]OK, thanks for the answers so far. I dont want to lose rows where it is zero so, I cant http://stackoverflow.com/questions/19631017/simple-way-to-prevent-a-divide-by-zero-error-in-sql accept a NULL in its place or to filter out those rows with a WHERE @int20 I simply want to replace a 0 value with a 1 value... more ▼ 0 total comments 973 characters / 178 words asked Oct 14, 2010 at 08:54 AM in Default Fatherjack ♦♦ 43.7k ● 79 ● 98 ● 117 edited Oct 14, 2010 at 11:03 AM add new comment (comments are locked) 10|1200 characters needed characters left ▼ Everyone Moderators Original poster and moderators Other... Viewable by all users 4 answers: sort voted first https://ask.sqlservercentral.com/questions/22402/best-way-to-avoid-divide-by-zero.html ▼ oldest newest voted first 0 You can use NULLIF if you want to return null when division by zero: SELECT @int1 / NULLIF(@int2,0) -- returns NULL or SELECT @int1 / ISNULL(NULLIF(@int2,0),1) -- returns @int1 The CASE statement is probably much faster then the second option, but you can test the performance more ▼ 11 total comments 288 characters / 46 words answered Oct 14, 2010 at 09:04 AM Håkan Winther 16.5k ● 36 ● 45 ● 57 edited Oct 14, 2010 at 09:07 AM Always though about CASE on this one. Good to know about NULLIF Oct 14, 2010 at 09:07 AM ozamora Håkan Winther Nope, the case statement is not any faster because NULLIF is the case statememt. The actuall NULLIF function definition is simply a case statement under the hood, but it has this nice compact form :) nullif (@a, @b) means case when @a = @b then null else @a end Oct 14, 2010 at 09:50 AM Oleg @Håkan Winther Please accept my apologies, It looks like I cannot see straight today, so I did not notice isnull. I do believe though that in this situation the case is going to be only very-very-slightly faster. Oct 14, 2010 at 11:07 AM Oleg @Håkan Winther I did not know how to test performance of the functions for that very reason (if the number of records is huge then IO and memory come into play and if the number of records is small then test is useless) until I saw how Jeff Moden does it. Brilliantly simple, just set the variable in the select, which will call the funct
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 https://www.bennadel.com/blog/984-using-nullif-to-prevent-divide-by-zero-errors-in-sql.htm final part in a four part series that discusses the SQL NULL value. https://sqljourney.wordpress.com/2010/07/02/fixing-error-8134-devided-by-zero-error-encountered-in-sql-server/ I haven't read the first three 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 call.The idea here is that, as with any other form of math that I know of, you cannot divide by zero in a divide by SQL call. Therefore, running this code:
2013(2) January 2013(2) December 2012(3) June 2011(1) August 2010(2) July 2010(4) June 2010(13) RSS - PostsRSS - Comments Your Email ID: Join 266 other followers SQL Journey SQL Journey Top Rated Blogroll Jessica M. Moss SSIS Talk SSIS Team Blog Exam Material for 70-463 SSIS Junkie Community SSIS Community Tasks and Components SQL Server QA Syntax Highlighter Samples Projects and Tools SQL Server Learning Center Design Center Database Modelling Samples Bookmarks Blog Stats 144,899 hits Get a Freelancer (function(d) { var po = d.createElement('script'); po.type = 'text/javascript'; po.async = true; po.src = '//static.flnaffiliate.com/build/js/affiliate-sdk.js'; var s = d.getElementsByTagName('script')[0]; s.parentNode.insertBefore(po, s); })(document); « Finding Nth highest number in SQLServer SQL Server: Keyboard Shortcuts - Part1 » Fixing Error: 8134 (Devided By Zero Error Encountered) in SQLServer Posted by Prashant on July 2, 2010 Problem: While performing mathematical operations it throws Divided by zero error. Msg 8134, Level 16, State 1, Line 5 Divide by zero error encountered. This situation often arises in production databases if the script has not been tested with sufficient data before putting the script to production database. This happens when a number is divided by 0 (zero). Solution: There can be many ways to handle this error. Here are some of my workarounds in SQL Server. Using NULLIF & ISNULL/ COALESCE Using CASE Using ARITHABORT & ANSI_WARNINGS Method: 1 SELECT ISNULL(Number1 / NULLIF(Number2, 0), 0) AS [Result] FROM tbl_err_8134 In this method uses NULLIF. In this case when the divisor is 0 (Zero) it will return NULL to the divisor, so the result will also became NULL. Then by IFNULL it returns 0 as the result is NULL here. Method: 2 SELECT CASE WHEN Number2 = 0 THEN 0 ELSE Number1 / Number2 END AS [Result] FROM tbl_err_8134 In this method uses CASE. Here when the divisor is 0 (Zero) it will return 0 as result or else the result will be division of two numbers. Method: 3 SET ARITHABORT OFF SET ANSI_WARNINGS OFF GO SELECT ISNULL(Number1 / Number2, 0) AS [Result] from tbl_err_8134 Here when ARITHABORT & ANSI_WARNINGS are set to OFF it will continue processing and will return NULL as a result. To know more about ARITHABORT you can follow this link. Download the complete script file here. TweetEmailPrintMoreShare on TumblrLike this:Like Load