Devide by zero

  • 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?

  • 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

     

  • 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