Re: Evading Divide by 0 errors

[Zach Ellis]

Hello Pete Rogers not sure if you still need help but this might help what your looking for. using the case function you can step though different scenarios
. the first when line outputs a 0 if YTD_Net_sales = 0 same thing with the second when line the third will output a 0 is the calculation returns a null value the last one returns the calculation that has two numbers in it.

select
case
when YTD_Net_sales = 0 then 0
when Previous_YTD_Net_sales = 0 then 0
when (((YTD_Net_sales - Previous_YTD_Net_sales)*100) / Previous_YTD_Net_sales) is null then 0
else (((YTD_Net_sales - Previous_YTD_Net_sales)*100) / Previous_YTD_Net_sales)
end as grow
from growth;

not sure how you make the report but i assume that the equation is in a select statement
growth is a table i made to practice the problem. growth table has some values in the two columns were one is 0 and the other has a number in it.

Hope this helps
Zach Ellis