Divided By Zero Error In Sql
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 divided by zero error in sql server Learn more about hiring developers or posting ads with us Stack Overflow Questions Jobs Documentation msg 8134 level 16 state 1 line 1 divide by zero error encountered Tags Users Badges Ask Question x Dismiss Join the Stack Overflow Community Stack Overflow is a community of 4.7 million programmers, just like sql divide by zero error nullif 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 down vote favorite 46 I have this error message: Msg 8134,
How To Fix Divide By Zero Error In Sql Server
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 a special treatment for zero. Is the best way to use a NullIf clause? Is there tsql divide by zero error encountered 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 Hooper 4241525 asked May 14 '09 at 6:06 Henrik Staun Poulsen 4,89331220 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 350 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,89331220 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 87 down vote In case you want to return zero, in case a zero devision would happen, you can use: SELECT COALESCE(dividend / NULLIF(divisor,0), 0) FROM sometable For every divisor that is zero, y
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 divide by zero error encountered excel Learn more about hiring developers or posting ads with us Stack Overflow Questions Jobs Documentation
Oracle Sql Divide By Zero
Tags Users Badges Ask Question x Dismiss Join the Stack Overflow Community Stack Overflow is a community of 4.7 million programmers, just like
Sql Nullif
you, helping each other. Join them; it only takes a minute: Sign 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 http://stackoverflow.com/questions/861778/how-to-avoid-the-divide-by-zero-error-in-sql used to cause a Divide By Zero exception, I've wrapped it in a CASE statement to 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 http://stackoverflow.com/questions/19631017/simple-way-to-prevent-a-divide-by-zero-error-in-sql 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 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 26 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.2k42940 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:3
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 https://ask.sqlservercentral.com/questions/22402/best-way-to-avoid-divide-by-zero.html AS INT SET @Int1 = 6 SET @Int2 = 2 SELECT @Int1 / @Int2 SET @Int1 = 6 SET @Int2 = 0 SELECT @Int1 / CASE WHEN @Int2 = 0 THEN 1 ELSE @Int2 END is there a better way of getting the http://sqlserverplanet.com/tsql/overcome-divide-by-zero-using-nullif same results but without littering by nice tidy 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 by zero dont want to lose rows where it is zero so, I cant 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 by zero error ▼ Everyone Moderators Original poster and moderators Other... Viewable by all users 4 answers: sort voted first ▼ 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
by Zero Using NULLIF Posted on April 5, 2012 by Derek Dieter 1 4 « A Query Method Faster Than BETWEENCreating Unique Random Numbers » Anytime we are dividing we need to think of the divide by zero scenario. Even if you think you will never encounter this with your result set, it's advisable to guard against it because when divide by zero is encountered, an error is thrown. The best method I've found to overcome this is by using the NULLIF function. This function takes two parameters and if they are equal, a NULL value is returned. Lets take a look at an example that throws a divide by zero error. DECLARE @iter FLOAT; DECLARE @num FLOAT SET @num = 10; SET @iter = 5; WHILE @iter > -5 BEGIN SELECT @num / @iter SET @iter = @iter - 1 END Running the following query, we see that once the variable @iter becomes zero, we receive an error. So the most elegant way to overcome this is to use NULLIF function and compare @iter to zero. When it does equal zero, it will instead change it to a null. And when dividing anything by NULL will equal a NULL. DECLARE @iter FLOAT; DECLARE @num FLOAT; SET @num = 10; SET @iter = 5; WHILE @iter > -5 BEGIN SELECT @num / NULLIF(@iter,0); SET @iter = @iter - 1; END This executes without error, however we still receive a null as a result. If you need otherwise, then you may want to wrap the equation in an ISNULL, to return a different value. DECLARE @iter FLOAT; DECLARE @num FLOAT; SET @num = 10; SET @iter = 5; WHILE @iter > -5 BEGIN SELECT ISNULL(@num / NULLIF(@iter,0),@num); SET @iter = @iter - 1; END This will just return the same number you are dividing by, if you encounter a NULL denominator. Filed under TSQL Related Posts: SQL Server For Each Row Next SQL Server Begin Try Concatenate Rows Using Coalesce Alter Index All Tables How to Concatenate SQL Rank SQL Replace Compare Stored Procedure Output by Creating a Test Harness Substring Between Two Characters Post a comment Comments (RSS) Trackback Permalink Click here to cancel reply. Name: Email: Comment: One comment Jiwa 29 Aug 2015 at 8:36 am ( 2012.02.11 08:02 ) : This feeder works great as long as you fololw the directions and complete ALL the steps for setting it up. After programming the current time, you set the times you want them to eat, THEN go back to those times and select 1 rotation or 2, depending on how much you want them to have. The default is 0, so that's why it didn't work for me at first. Now that I have it programmed correctly it works great, right on schedule. The mounting bracket did not fit on the edge of my tank and it comes with d