Divide By Zero Error In T-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 Learn more about hiring developers or posting ads with us t sql divide by zero error encountered Stack Overflow Questions Jobs Documentation Tags Users Badges Ask Question x Dismiss Join the Stack Overflow Community
Divide By Zero Error Encountered. In Sql Server
Stack 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
Divide By Zero Error Encountered In Sql Server 2012
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 best way to write SQL
T Sql Avoid Divide By Zero
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 4241525 asked May 14 '09 at 6:06 Henrik transact sql divide by zero 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 ISNULL. However, COALESCE is in the standards so is more portable. –Paul Chernoch Jul 12 '12 at 1
3001-4000 Messages 4001-5000 Messages 5001-6000 Messages 6001-7000 Messages 7001-7500 Messages 7501-8000 Messages 8001-8500 Messages 8501-9000 Messages 9001-9500 Messages 9501-10000 Messages 10001-10500 Messages 10501-11000 Messages 11001-11500 Messages 11501-12000 Messages 12001-13000 Messages 13001-13500 tsql divide by zero error encountered Messages 14001-14500 Home>SQL Server Error Messages> Msg 8134 - Divide by zero error encountered mssql divide by zero error encountered SQL Server Error Messages - Msg 8134 - Divide by zero error encountered SQL Server Error Messages - Msg 8134 divide by zero error java Error Message Server: Msg 8134, Level 16, State 1, Line 1 Divide by zero error encountered. Causes: This error is caused by performing a division operation wherein the denominator or the divisor is http://stackoverflow.com/questions/861778/how-to-avoid-the-divide-by-zero-error-in-sql 0. This error is not encountered when the denominator or divisor is NULL because this will result to a NULL value. Solution / Work Around: There are three ways to avoid the "Division by zero encountered" error in your SELECT statement and these are as follows: CASE statement NULLIF/ISNULL functions SET ARITHABORT OFF and SET ANSI_WARNINGS OFF Using the CASE statement, your query will look like the following: http://www.sql-server-helper.com/error-messages/msg-8134.aspx SELECT CASE WHEN [Denominator] = 0 THEN 0 ELSE [Numerator] / [Denominator] END AS [Percentage] FROM [Table1] If the denominator or divisor is 0, the result becomes 0. Otherwise, the division operation is performed. Using the NULLIF and ISNULL functions, your query will look like the following: SELECT ISNULL([Numerator] / NULLIF([Denominator], 0), 0) AS [Percentage] FROM [Table1] What this does is change the denominator into NULL if it is zero. Then in the division, any number divided by NULL results into NULL. So if the denominator is 0, then the result of the division will be NULL. Then to return a value of 0 instead of a NULL value, the ISNULL function is used. Lastly, using the SET ARITHABORT and SET ANSI_WARNINGS, your query will look like the following: SET ARITHABORT OFF SET ANSI_WARNINGS OFF SELECT [Numerator] / [Denominator] With both ARITHABORT and ANSI_WARNINGS set to OFF, SQL Server will return a NULL value in a calculation involving a divide-by-zero error. To return a 0 value instead of a NULL value, you can put the division operation inside an ISNULL function: SET ARITHABORT OFF SET ANSI_WARNINGS OFF SELECT ISNULL([Numerator] / [Denominator], 0) Related Articles : Frequently Asked Questions - SQ
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 https://ask.sqlservercentral.com/questions/22402/best-way-to-avoid-divide-by-zero.html 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 = 6 SET @Int2 = 2 https://msdn.microsoft.com/en-us/library/ms184341.aspx 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 divide by 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 is zero so, I cant accept a NULL in its place or to divide by zero 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 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 Winth
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 Retired content Samples 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 Warehouse Controls whether error messages are returned from overflow or divide-by-zero errors during a query. Transact-SQL Syntax ConventionsSyntax Copy -- Syntax for SQL Server and Azure SQL Database SET ARITHIGNORE { ON | OFF } [