Microsoft Sql Divide By Zero Error
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 oracle sql divide by zero more about hiring developers or posting ads with us Stack Overflow Questions Jobs Documentation Tags Users
Msg 8134 Level 16 State 1 Line 1 Divide By Zero Error Encountered
Badges Ask Question x Dismiss Join the Stack Overflow Community Stack Overflow is a community of 6.2 million programmers, just like you, helping divide by zero error encountered excel 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 used to cause sql nullif 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.6k43873 1 It depends on how you wan't your query and result to behave when AttTotal is 0
Divide By Zero Error Encountered In Stored Procedure
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 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
8 knows that division by zero is a no-no, or at least they should. Everybody who's used SQL for any amount of time knows that we can use a CASE expression to prevent division by zero. But do you know the sql nullif 0 other way to prevent division by zero in SQL? Assume a database file called SomeTable, with redshift nullif fields called Quantity and Amount. Let's divide Amount by Quantity. First, here's the usual case statement. select itnbr, Amount, Quantity, case when Quantity <>
Divide By 0 In 128 Bit Arithmetic Netezza
0 then Amount / Quantity else 0 end from SomeTable If Quantity is not zero, the division takes place. If Quantity is zero, the calculation yields a zero. We could just as easily return any other numeric value, the value http://stackoverflow.com/questions/19631017/simple-way-to-prevent-a-divide-by-zero-error-in-sql one, for example. select itnbr, Amount, Quantity, case when Quantity <> 0 then Amount / Quantity else 1 end from SomeTable The other way to prevent division by zero is to use the NULLIF function. NULLIF requires two arguments. If the arguments are equal, NULLIF returns a null value. If they are not equal, NULLIF returns the first value. Here's the SQL. select itnbr, Amount, Quantity, Amount / nullif(Quantity,0) from SomeTable If Quantity is not zero, the two arguments do not http://www.itjungle.com/fhg/fhg051210-story02.html match, and the resulting calculation is Amount divided by Quantity. But if Quantity is zero, the arguments do match, and the calculation is Amount divided by null, which yields a null value. If you use this technique, be sure to put the zero in the second argument of NULLIF. Post this story to del.icio.us Post this story to Digg Post this story to Slashdot Sponsored By SEQUEL SOFTWARE SEQUEL ViewPoint--Data Access & Analysisfor Power Systems Servers Easy to use by IT and end users Real-time data access and analysis Queries, reports, pivot tables, drill-down analysis, kpi dashboards IBM i-centric with multiplatform data access Expert support and training Secure data access GUI, Web & green screen interface SEQUEL--Fast, efficient & cost-effective data analysis Click here for a FREE Information Kit! Senior Technical Editor: Ted Holt Technical Editor: Joe Hertvik Contributing Technical Editors: Erwin Earley, Brian Kelly, Michael Sansoterra Publisher and Advertising Director: Jenny Thomas Advertising Sales Representative: Kim Reed Contact the Editors: To contact anyone on the IT Jungle Team Go to our contacts page and send us a message. Sponsored Links Help/Systems: Robot/SCHEDULE Enterprise for UNIX, Linux, Windows & i looksoftware: RPG Open Access Webinar - May 18 at 10am (GMT) & May 19 at 2pm (EDT) Essex Technology Group: May 18-20: IBM POWER7 + COGNOS + VISION, NYC + PA + NJ IT Jungle Store Top Book Pick
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 https://ask.sqlservercentral.com/questions/22402/best-way-to-avoid-divide-by-zero.html = 6 SET @Int2 = 2 SELECT @Int1 / @Int2 SET @Int1 = 6 SET @Int2 = https://msdn.microsoft.com/en-us/library/ms184341.aspx 0 SELECT @Int1 / 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 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 divide by 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 and moderators Other... Viewable by divide by zero 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 is useless) until I saw how Jeff Moden does it. B
resources Windows Server 2012 resources Programs MSDN subscriptions Overview Benefits Administrators Students Microsoft Imagine Microsoft Student Partners ISV Startups TechRewards Events Community Magazine Forums Blogs Channel 9 Documentation APIs and reference Dev centers Samples Retired content We’re sorry. The content you requested has been removed. You’ll be auto redirected in 1 second. Microsoft SQL Server Language Reference Transact-SQL Reference (Database Engine) SET Statements (Transact-SQL) SET Statements (Transact-SQL) SET ARITHIGNORE (Transact-SQL) SET ARITHIGNORE (Transact-SQL) SET ARITHIGNORE (Transact-SQL) SET ANSI_DEFAULTS (Transact-SQL) SET ANSI_NULL_DFLT_OFF (Transact-SQL) SET ANSI_NULL_DFLT_ON (Transact-SQL) SET ANSI_NULLS (Transact-SQL) SET ANSI_PADDING (Transact-SQL) SET ANSI_WARNINGS (Transact-SQL) SET ARITHABORT (Transact-SQL) SET ARITHIGNORE (Transact-SQL) SET CONCAT_NULL_YIELDS_NULL (Transact-SQL) SET CONTEXT_INFO (Transact-SQL) SET CURSOR_CLOSE_ON_COMMIT (Transact-SQL) SET DATEFIRST (Transact-SQL) SET DATEFORMAT (Transact-SQL) SET DEADLOCK_PRIORITY (Transact-SQL) SET FIPS_FLAGGER (Transact-SQL) SET FMTONLY (Transact-SQL) SET FORCEPLAN (Transact-SQL) SET IDENTITY_INSERT (Transact-SQL) SET IMPLICIT_TRANSACTIONS (Transact-SQL) SET LANGUAGE (Transact-SQL) SET LOCK_TIMEOUT (Transact-SQL) SET NOCOUNT (Transact-SQL) SET NOEXEC (Transact-SQL) SET NUMERIC_ROUNDABORT (Transact-SQL) SET OFFSETS (Transact-SQL) SET PARSEONLY (Transact-SQL) SET QUERY_GOVERNOR_COST_LIMIT (Transact-SQL) SET QUOTED_IDENTIFIER (Transact-SQL) SET REMOTE_PROC_TRANSACTIONS (Transact-SQL) SET ROWCOUNT (Transact-SQL) SET SHOWPLAN_ALL (Transact-SQL) SET SHOWPLAN_TEXT (Transact-SQL) SET SHOWPLAN_XML (Transact-SQL) SET STATISTICS IO (Transact-SQL) SET STATISTICS PROFILE (Transact-SQL) SET STATISTICS TIME (Transact-SQL) SET STATISTICS XML (Transact-SQL) SET TEXTSIZE (Transact-SQL) SET TRANSACTION ISOLATION LEVEL (Transact-SQL) SET XACT_ABORT (Transact-SQL) TOC Collapse the table of content Expand the table of content This documentation is archived and is not being maintained. This documentation is archived and is not being maintained. SET ARITHIGNORE (Transact-SQL) Other Versions SQL Server 2012 THIS TOPIC APPLIES TO: SQL Server (starting with 2008)Azure SQL DatabaseAzure SQL Data Warehouse Parallel Data