Fixing Divide by Zero Error

  • Hello,

    I have in select statement select t1.A/t1.B but get divide by zero error

    when I used with isnull isnull(t1.A/t1.B,'-')

    I get error Error converting data type varchar to float.

    how to fix?

    Thanks

  • VegasL (4/15/2013)


    Hello,

    I have in select statement select t1.A/t1.B but get divide by zero error

    when I used with isnull isnull(t1.A/t1.B,'-')

    I get error Error converting data type varchar to float.

    how to fix?

    Thanks

    select t1.A / nullif(t1.B,0)

  • VegasL (4/15/2013)


    Hello,

    I have in select statement select t1.A/t1.B but get divide by zero error

    when I used with isnull isnull(t1.A/t1.B,'-')

    I get error Error converting data type varchar to float.

    how to fix?

    Thanks

    You can handle this using CASE also:

    SELECT CASE when t1.b =0 THEN 0 ELSE t1.A/t1.B

    _______________________________________________________________
    To get quick answer follow this link:
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

  • why are you storing numeric values in a varchar column. As experienced, this will get you in troubles.

    ( in most cases because of formatting issues or just because someone put non-numeric data in it)

    There must be a zero ( 0 ) in your t1.B column.

    add a case statement to check it so it doesn't produce the error.

    select t1.a / case t1.b when 0 then NULL /* to the result will be null */

    else t1.B

    end as TheResult

    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

  • Thanks Lynn, you'res was the easiest solution for me. I'll have to look into the others more. Thanks All!

  • I would agree with Lynn's post. You can not divide by zero, but if you divide by NULL, you'll get NULL.

    DECLARE @i FLOAT = 10, @x FLOAT = 0

    SELECT @i / NULLIF(@x,0)

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply