Divide By Zero Error Encountered In Sql 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 ads with us Stack divide by zero error encountered in sql server Overflow Questions Jobs Documentation Tags Users Badges Ask Question x Dismiss Join the Stack Overflow Community Stack
Divide By Zero Error Encountered In Sql Server 2012
Overflow is a community of 4.7 million programmers, just like you, helping each other. Join them; it only takes a minute: Sign up How to avoid divide by zero error encountered sql server 2008 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 best way to write SQL code so
Sql Divide By Zero Error Encountered Nullif
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 4241525 asked May 14 '09 at 6:06 Henrik Staun Poulsen 4,89331220 tsql divide by zero error encountered 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 ISNULL. However, COALESCE is in the standards so is more portable. –Paul Chernoch Jul 12 '12 at 14:29 16 If someone else doesn
up Recent PostsRecent Posts Popular TopicsPopular Topics Home Search Members Calendar Who's On Home » SQL Server
Oracle Sql Divide By Zero
2008 » T-SQL (SS2K8) » "Divide by zero error encountered." in where...
Sql Nullif
"Divide by zero error encountered." in where clause on SQL 2008 and working OK on SQL 2000 Rate Topic msg 8134 level 16 state 1 line 1 divide by zero error encountered Display Mode Topic Options Author Message logavinalogavina Posted Wednesday, March 14, 2012 3:30 PM Forum Newbie Group: General Forum Members Last Login: Tuesday, February 4, 2014 9:34 AM Points: 6, Visits: http://stackoverflow.com/questions/861778/how-to-avoid-the-divide-by-zero-error-in-sql 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 <> 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 <> http://www.sqlservercentral.com/Forums/Topic1267123-392-1.aspx 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, 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 fail
(Русский)ישראל (עברית)المملكة العربية السعودية (العربية)ไทย (ไทย)대한민국 (한국어)中华人民共和国 (中文)台灣 (中文)日本 (日本語) HomeLibraryLearnDownloadsTroubleshootingCommunityForums Ask a question Quick access Forums home Browse forums users FAQ Search related threads Remove From My Forums Answered by: Divide https://social.msdn.microsoft.com/Forums/sqlserver/en-US/83ea36f4-2fcc-46cc-b2a5-f4725b7eda94/divide-by-zero-error-encountered?forum=transactsql by zero error encountered. SQL Server > Transact-SQL Question 0 Sign in to vote My code is: 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 (Credit_Amount>0 AND Overdue_Amount/Credit_Amount>0.1) And I got following error: Msg divide by 8134, Level 16, State 1, Line 1 Divide by zero error encountered. If I change it like this: 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 divide by zero 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) 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 diffe