# Divide by zero error encountered.

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

• 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 Friday, February 19, 2010 6:41 PM
Friday, February 19, 2010 6:36 PM

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

Friday, February 19, 2010 6:35 PM
• 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 Friday, February 19, 2010 6:41 PM
Friday, February 19, 2010 6:36 PM
• 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
• EXCELENTTTT :D:D:D

TANKSSSSSS
Friday, February 19, 2010 6:43 PM