August 6, 2004 at 2:48 am
When I try to execute Sql query in a Query Analyzer I am getting following error
Server: Msg 8134, Level 16, State 1, Line 1
Divide by zero error encountered.
Can anyone suggest me how to handle divided by zero error in Sql?
August 6, 2004 at 3:19 am
Without the code this is an almost impossible question...however I am going to assume that you are running a very simple query and are dividing one field by another.
I assume you have something like this in your query
select fieldOne/field2 as MyResult
from mytable
and because field2 can be zero you will encounter a divide by zero
so yoou protect the query by using a where clause as follows
where field2<>0
which means that you will never get a divide by zero condition in your query
Now you can use error checking in TSQL (see the raiseerror statement and associated structures in BOL) in your code but that should not be your first thought. Conceptually any calculation that results in a divide by zero is undefined i.e. meaningless. In the above example the problem isnt the query it is the data. You should do some data analysis and if you have a field used commonly as a divisor then you must be expecting a non zero value in that field. REpair the data to reflect what the default for that field should be and add a default constraint so that the field can never be zero in the future.
Hope this helps
August 6, 2004 at 6:36 am
Divide by zero always returns null, the combination of ANSI_WARNINGS, ARITHABORT and ARITHIGNORE determines whether an error is produced or at what level the abort takes place (see BOL)
So
SET ANSI_WARNINGS OFF
SET ARITHABORT OFF
SELECT ISNULL(fieldOne/field2,0) as MyResult
will result in zero
Far away is close at hand in the images of elsewhere.
Anon.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply