none
Divide by zero error encountered. RRS feed

  • Question

  • Hello.... I have a problem. :(
     My code is:

    select st.ref, st.design, st.marg1,st.epv1,dfcl.desc1,st.epcult,st.epv1*(1-(dfcl.desc1)/100)/(ISNULL(st.epcult,0)) as ssss 
    from st (nolock) inner join dfcl (nolock) on st.tipodesc= dfcl.ref 
    Where dfcl.tipodesc='9-GENERICO' 
    order by st.ref
    after that i have error

    Msg 8134, Level 16, State 1, Line 13

    Divide by zero error encountered.

    Friday, February 19, 2010 6:29 PM

Answers

  • Instead of ISNULL(st.EpCult,0) use NULLIF(st.EpCult,0) -- so it will turn the 0 into NULL and you would not get an error. You need to ISNULL the whole expression if you want to get 0 in case of 0 for the divider. In other words:
    select st.ref, st.design, st.marg1,st.epv1,dfcl.desc1,
    st.epcult,ISNULL(st.epv1*(1-(dfcl.desc1)/100)/(NULLIF(st.epcult,0)),0) as ssss 
    from st (nolock) inner join dfcl (nolock) on st.tipodesc= dfcl.ref 
    Where dfcl.tipodesc='9-GENERICO' 
    order by st.ref
    


    Premature optimization is the root of all evil in programming. (c) by Donald Knuth

    Naomi Nosonovsky, Sr. Programmer-Analyst

    My blog
    • Marked as answer by Night_devil Friday, February 19, 2010 6:41 PM
    Friday, February 19, 2010 6:36 PM
    Moderator

All replies

  • I think perhaps you meant to use NULLIF instead of ISNULL?

    In other words, in your query, if ST.EPCult is NULL, then it uses 0 instead and therefore it will divide by 0.

    If you use NULLIF instead, then that means that if ST.EPCult is 0 then it changes it to NULL and therefore the entire "ssss" expression will be NULL.

    What do you want to occur?

    --Brad (My Blog)
    Friday, February 19, 2010 6:35 PM
    Moderator
  • Instead of ISNULL(st.EpCult,0) use NULLIF(st.EpCult,0) -- so it will turn the 0 into NULL and you would not get an error. You need to ISNULL the whole expression if you want to get 0 in case of 0 for the divider. In other words:
    select st.ref, st.design, st.marg1,st.epv1,dfcl.desc1,
    st.epcult,ISNULL(st.epv1*(1-(dfcl.desc1)/100)/(NULLIF(st.epcult,0)),0) as ssss 
    from st (nolock) inner join dfcl (nolock) on st.tipodesc= dfcl.ref 
    Where dfcl.tipodesc='9-GENERICO' 
    order by st.ref
    


    Premature optimization is the root of all evil in programming. (c) by Donald Knuth

    Naomi Nosonovsky, Sr. Programmer-Analyst

    My blog
    • Marked as answer by Night_devil Friday, February 19, 2010 6:41 PM
    Friday, February 19, 2010 6:36 PM
    Moderator
  • You need to make the following non-zero:

    ISNULL(st.epcult,0)

    Potential work-around:

    ISNULL(st.epcult,1) or some other non-zero number.

    You can also use the CASE function.

    You can also trap it with TRY-CATCH.



    Kalman Toth, SQL Server & BI Training, SSAS OLAP, SSIS, SSRS; http://www.SQLUSA.com
    Friday, February 19, 2010 6:37 PM
    Moderator
  • EXCELENTTTT :D:D:D

    TANKSSSSSS
    Friday, February 19, 2010 6:43 PM