Divide By Zero Error Encountered. In Sql Server 2000
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 divide by zero error encountered in sql server 2012 ads with us Stack Overflow Questions Jobs Documentation Tags Users Badges Ask Question x Dismiss Join divide by zero error encountered sql server 2008 the Stack Overflow Community Stack Overflow is a community of 4.7 million programmers, just like you, helping each other. Join them; it only takes a divide by zero error encountered sql server 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, Level 16, State 1, Line 1 Divide by zero error encountered. What is the divide by zero error encountered excel 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 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
Msg 8134 Level 16 State 1 Line 1 Divide By Zero Error Encountered
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, you will get a zero in the result set. share|improve this answer edited Jan 15 '13 at 19:41 Peter Mortensen 10.2k1369107 answered Jan 4 '12 at 12:06 Tobias Domhan 1,4431011 8 Some benchmarks reveal that COALESCE is slightly slower than ISN
Recent PostsRecent Posts Popular TopicsPopular Topics Home Search Members Calendar Who's On Home » SQL Server 2008 » T-SQL (SS2K8) » "Divide by zero error encountered." in where... "Divide by zero
Oracle Sql Divide By Zero
error encountered." in where clause on SQL 2008 and working OK on SQL 2000 Rate sql nullif Topic Display Mode Topic Options Author Message logavinalogavina Posted Wednesday, March 14, 2012 3:30 PM Forum Newbie Group: General Forum Members Last Login: divide by zero error encountered in stored procedure Tuesday, February 4, 2014 9:34 AM Points: 6, Visits: 43 This query was working on SQL 2000 but on SQL 2008 is giving me the error "Divide by zero error encountered.":UPDATE T_PRICESET VAR = CASE WHEN PRICE <> http://stackoverflow.com/questions/861778/how-to-avoid-the-divide-by-zero-error-in-sql 0 AND COST <> 0 THEN ROUND((((COST-PRICE)/COST)*100),0) ELSE 999 END FROM T_PRICEWHERE ((PRICE <> 0 AND COST <> 0) AND (VAR <> ROUND((((COST-PRICE)/COST)*100),0))) OR ((PRICE = 0 OR COST = 0) AND VAR <> 999) OR (VAR IS NULL)Can anybody help, with this where clause, but logic has to stay the same? Post #1267123 Lynn PettisLynn Pettis Posted Wednesday, March 14, 2012 3:34 PM SSC-Insane Group: General Forum Members Last Login: Yesterday @ 8:11 PM Points: 23,393, http://www.sqlservercentral.com/Forums/Topic1267123-392-1.aspx Visits: 37,418 Please read the first article I reference below in my signature block below regarding asking for help. Follow the instructions on what to post and how. In this case, make sure your sample data has data that works using SQL Server 2000 and fails using SQL Server 2008. Lynn PettisFor better assistance in answering your questions, click hereFor tips to get better help with Performance Problems, click hereFor Running Totals and its variations, click here or when working with partitioned tablesFor more about Tally Tables, click hereFor more about Cross Tabs and Pivots, click here and hereManaging Transaction LogsSQL Musings from the Desert Fountain Valley SQL (My Mirror Blog) Post #1267124 diamondgmdiamondgm Posted Thursday, March 15, 2012 12:06 AM SSC-Enthusiastic Group: General Forum Members Last Login: Tuesday, May 3, 2016 12:12 AM Points: 149, Visits: 936 My guess is that failure is occurring at evaluation of (VAR <> ROUND((((COST-PRICE)/COST)*100),0))) OIn the WHERE clause.But its just a guess without sample data.PS.That evaluation seems a little redundant? Post #1267256 Jeff ModenJeff Moden Posted Thursday, March 15, 2012 12:20 AM SSC-Forever Group: General Forum Members Last Login: Yesterday @ 5:02 PM Points: 41,497, Visits: 38,832 I believe the answer is simple. You have two different servers. Are you absolutely sure the data is on the new server doesn't have a "0" in it somewhere for the Cost
(Русский)ישראל (עברית)المملكة العربية السعودية (العربية)ไทย (ไทย)대한민국 (한국어)中华人民共和国 (中文)台灣 (中文)日本 (日本語) HomeLibraryLearnDownloadsTroubleshootingCommunityForums Ask a question Quick access Forums home Browse forums users FAQ Search related threads Remove From My Forums Answered by: Divide by zero error encountered. SQL Server > Transact-SQL Question https://social.msdn.microsoft.com/Forums/sqlserver/en-US/83ea36f4-2fcc-46cc-b2a5-f4725b7eda94/divide-by-zero-error-encountered?forum=transactsql 0 Sign in to vote My code is: SELECT * FROM ( http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=86596 SELECT 20 AS Overdue_Amount, 100 AS Credit_Amount UNION ALL SELECT 0 AS Overdue_Amount, 0 AS Credit_Amount ) T WHERE Credit_Amount=100 OR (Credit_Amount>0 AND Overdue_Amount/Credit_Amount>0.1) And I got following error: Msg 8134, Level 16, State 1, Line 1 Divide by zero error encountered. If I change it like this: divide by SELECT * FROM ( SELECT 20 AS Overdue_Amount, 100 AS Credit_Amount UNION ALL SELECT 0 AS Overdue_Amount, 0 AS Credit_Amount ) T WHERE Credit_Amount=100 OR (ISNULL(Overdue_Amount/NULLIF(Credit_Amount,0),0)>0.1) So my quesion is "Why I encountered error when exec first snippet? What happened?" Tuesday, September 14, 2010 8:59 AM Reply | Quote Answers 1 Sign in to vote interestingly there was an (admittedly old) divide by zero technet discussion (http://technet.microsoft.com/en-gb/cc678236.aspx)where an MS guy says that short-circuiting is present in SQL Server. "Present" is not a very exact term. The SQL language "allow" for short cut. That is a good thing. Imagine: WHERE c1 = 23 and c2 = 45 Also, imagine an index on c1. Now, if SQLwere'nt allowed to do short cut (which would be ridiculous), then the index on c1 would be useful since c2 would have to be evaluated even for the rows where c1 is false. This is what I mean by shortcut is *allowed* (amd, again, anything else would be stupid). But, shortcut is not *required* since that would make SQL a less declarative language, considering the optimizer whold have few options to run the query. A slightly different example (read closely) WHERE c1 = 23 and c2 = 45 Now we imagine an index on c2 but not on c1. If short cut were required, then SQL Server would have to evaluate c1 = 23 first, and since we have no index on c1 we would have a table scan (even wi
SQL Server experts to answer whatever question you can come up with. Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums. SQL Server Forums Profile | ActiveTopics | Members | Search | ForumFAQ Register Now and get your question answered! Username: Password: Save Password Forgot your Password? All Forums SQL Server 2005 Forums Transact-SQL (2005) debugging "Divide by zero error encountered" Reply to Topic Printer Friendly Author Topic mike123 Flowing Fount of Yak Knowledge 1462 Posts Posted-07/19/2007: 09:48:03 Hi,I have a query that I'm getting this error on. I'm wondering if there are any simple tricks to debugging SPROCS when this occurs?I'm having problems figuring out exactly where the error is occuring, and also would like to know the best way to prevent this.Thanks very much!mike123 sshelper Posting Yak Master 216 Posts Posted-07/19/2007: 09:58:56 There are 3 ways of avoiding the Divide by zero error and you can refer to the following link for these:http://www.sql-server-helper.com/error-messages/msg-8134.aspxhttp://www.sql-server-helper.com/faq/select-p01.aspx (Question #10).SQL Server Helperhttp://www.sql-server-helper.com mike123 Flowing Fount of Yak Knowledge 1462 Posts Posted-07/19/2007: 10:34:55 thanks a bunch! I still couldnt find the divide by zero error somehow, for now I have successfully used SET ARITHABORT OFFSET ANSI_WARNINGS OFFAre there any downsides to using this ? Performance hits?Thanks again,Mike123 Topic Reply to Topic Printer Friendly Jump To: Select Forum General SQL Server Forums New to SQL Server Programming New to SQL Server Administration Script Library Data Corruption Issues Database Design and Application Architecture SQL Server 2012 Forums Transact-SQL (2012) SQL Server Administration (2012) SSIS and Import/Export (2012) Analysis Server and Reporting Services (2012) Replication (2012) Availability Groups and DR (2012) Other SQL Server 2012 Topics SQL Server 2008 Forums Transact-SQL (2008) SQL Server Administration (2008) SSIS and Import/Export (2008) High Availability (2008) Replication (2008) Analysis Server and Reporting Services (2008) Other SQL Server 2008 Topics SQL Server 2005 Forums Transact-SQL (2005) SQL Server Administration (2005) .NET Inside SQL Server (2005) SSIS and Import/Export (2005) Service Broker (2005) Replication (2005) High Availability (2005) Analysis Server and Reporting Services (2005) Express Edition and Compact Edition (2005) Other SQL Server Topics (2005) SQL Server 2000 Forums SQL Server Development (2000) SQL Server Administration (2000) Import/Export (DTS) and Replication (2000) Transact-SQL (200