How to fix "Divide by zero error" in SQL Server
-
Hi All,
Anyone know how to fix the error "Divide by zero error encountered. [SQLSTATE 22012] (Error 8134) "
Thanks in advance
-snt
-
Don't divide by zero.
You need to figure out where you are doing this and fix it. Derrick Leggett
Mean Old DBA
When life gives you a lemon, fire the DBA. -
Prior to any division check the divisor for its value, if zero take a different path in code.
Cheers
-
Like Derrick Leggett mentioned, figure it out !
If your investigation points the symantics are OK, then you might use a case statement .
declare @col1 integer
declare @col2 integer
declare @col3 integer
select @col1 = 8, @col2 = 0, @col3 = 2
select @col1 / case when @col2 = 0 then 1 else @col2 end as first_division
, @col1 / case when @col3 = 0 then 1 else @col3 end as second_division
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
-
You can do the same thing on the application as well if you are using one. In crystal reports and business objects I never create variables using division without setting the value to 1 in the case of zero or null values.
You can use if then else or isnull to achieve this.
------------------------------
The Users are always right - when I'm not wrong! -
You might want to toggle SET ARITHABORT ON|OFF and SET ARITHIGNORE ON|OFF. Check BOL for more details.
-
I'm getting the same message. I'm running a single step agent job executing a single proc, and the agent returns this detail:
Divide by zero error encountered. [SQLSTATE 22012] (Error 8134) The statement has been terminated. [SQLSTATE 01000] (Error 3621). The step failed.
Within the proc that is being run, I log every command called within the proc except the actual calls to the log (over 300 commands), and the log table shows that the proc completed successfully. I'm thinking this is an agent error, but I do not know. Where should I look at this point? Thanks. John
-
like barsuk wrote
What are you current connection settings of
SET ARITHABORT ON|OFF (abort batch on error)
and SET ARITHIGNORE ON|OFF. (issue a warning but continue)
-
Im running a Crystal Report from a SQL Procedure script in SQL 2008
Ive just exoerienced this same error message and solved it by inserting WHEN 0 THEN 0 to the beginning of my script ( first few lines shown here)
PROCEDURE [xxdbo].[CH_OperationAnalysis] @FromDate DATETIME, @ToDate DATETIME AS
SELECT
[tse].[Operation],
[tse].[OperationDesc],
[tse].[JobNo],
CASE [tse].[OperationLength]
WHEN 0 THEN 0
ELSE
( etc etc etc
Hope this helps
-
I always use select a/nullif(b,0) for division when there is a possibility that the divisor is zero.
-
The solution Edward Boyle posted is the one I use. If you need a non-null final result, simply add an encompassing COALESCE or ISNULL to provide the appropriate 'default' value.
ISNULL(col1 / NULLIF(col2, 0), 0) will provide a final result of zero.
-
Guys you're posting answers to a question asked in 2004. I hope they've fixed the problem by now. :w00t:
-
hahah so do I
-
select COALESCE(MIN(Act) / NULLIF(MAX(Act),0), 0) as minvsmaxAct from table_name
-
priterande (11/5/2015)
select COALESCE(MIN(Act) / NULLIF(MAX(Act),0), 0) as minvsmaxAct from table_nameThis is what I usually do, except that I don't reply to 5 year old threads. :w00t:
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply