Divide By Zero Error Encountered 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
Divide By Zero Error Encountered In Sql Server
about Stack Overflow the company Business Learn more about hiring developers or posting ads divide by zero error encountered in sql server 2012 with us Stack Overflow Questions Jobs Documentation Tags Users Badges Ask Question x Dismiss Join the Stack Overflow Community Stack Overflow
Divide By Zero Error Encountered Sql Server 2008
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 the “divide by zero” error in SQL? up vote sql divide by zero error encountered nullif 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 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 tsql divide by zero error encountered 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 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|
Recent PostsRecent Posts Popular TopicsPopular Topics Home Search Members Calendar Who's On
Oracle Sql Divide By Zero
Home » SQL Server 2008 » T-SQL (SS2K8) » "Divide by zero msg 8134 level 16 state 1 line 1 divide by zero error encountered error encountered." in where... "Divide by zero error encountered." in where clause on SQL 2008 and working
Divide By Zero Error Encountered Excel
OK on SQL 2000 Rate Topic Display Mode Topic Options Author Message logavinalogavina Posted Wednesday, March 14, 2012 3:30 PM Forum Newbie Group: General Forum Members Last Login: Tuesday, http://stackoverflow.com/questions/861778/how-to-avoid-the-divide-by-zero-error-in-sql 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 <> 0 AND COST <> 0 THEN ROUND((((COST-PRICE)/COST)*100),0) ELSE 999 END FROM T_PRICEWHERE ((PRICE <> 0 AND COST <> 0) AND http://www.sqlservercentral.com/Forums/Topic1267123-392-1.aspx (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, 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 diamondgmdiamo
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 https://ask.sqlservercentral.com/questions/22402/best-way-to-avoid-divide-by-zero.html 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 same results https://www.techonthenet.com/access/queries/divide_by_zero.php 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 dont want to lose divide by 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 ▼ Everyone Moderators Original poster divide by zero 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 and if the number of records is small then test
MariaDB PostgreSQL SQLite MS Office Excel Access Word Web Development HTML CSS Color Picker Languages C Language More ASCII Table Linux UNIX Java Clipart Techie Humor Advertisement Access Topics Combo Boxes Constants Database Date/Time Forms Functions Modules/VBA Queries Question/Answer Reports Security Shortcuts Standards Subforms Switchboard Tables Text boxes MS Access 2003: Handling Divide by Zero errors in queries This MSAccess tutorial explains how to handle divide by zero errors in queries in Access 2003 (with screenshots and step-by-step instructions). See solution in other versions of Access: Access 2007 Access 2003 Question: In Microsoft Access 2003/XP/2000/97, I'm trying to write a formula in a query as follows: [Price]/[Quantity] Most of the times this formula works, but in some cases the [Quantity] field is zero so when the formula divides zero by zero, the result comes up as #Error. Is there a way I can tell Access, if dividing by zero, the result is zero? Answer: You can use the iif function in your Access query to handle these cases. We'll demonstrate how to do this with the example below. In this example, we've used the iif function to return 0 if the [Quantity] is 0. Otherwise, it would return the value of [Price] divided by [Quantity]. This is achieved with the following formula: IIf([Quantity]=0,0,[Price]/[Quantity]) Now, your Access query should no longer return an error when a [Quantity] of 0 is encountered. Share this page: Advertisement Back to top Home | About Us | Contact Us | Testimonials | Donate While using this site, you agree to have read and accepted our Terms of Service and Privacy Policy. We use advertisements to support this website and fund the development of new content. Copyright © 2003-2016 TechOnTheNet.com. All rights reserved.