asked on

resolve devide by zero error

I have a procedure calling some data from a view which has the where clause to Eliminates zero values, but I still get Devide by zero error. also, can anyone provide a better syntaxt to eliminate those zero's in one query?
code attached. I would basically like extracked a list of Items where the usage for this period have doubled what the last 2 months were and M1 is more than half of what we have.

select * from vw_spike_usages2 
group by item_id ,qty_free,M3,M2,M1
having  M1/M2+M3>=1 and M1/qty_free >=.5

Open in new window

peter57r

I have no idea what your expression mean but are you sure that ....
M1/M2+M3>=1
is correct and that you don't mean....
M1/(M2+M3)>=1
which is quite different as a mathematical expression.

tips54

ASKER

thanks.
I added the parenthesis. Can you think of any way to address the divide by zero error?

peter57r

What does this mean then?
'data from a view which has the where clause to Eliminates zero values'

or are you just testing individual field values and not removing records where m2+m3 is zero?

Christopher Gordon

Not sure if I followed your example, but using Nullif([fieldname],0) always resolved this issue for me. So in your example,

M1/NULLIF((m2+m3),0) > = 1

ksparky

You might try this:

SELECT *
FROM vw_spike_usages2
WHERE ISNULL(M1,-1)>0 AND ISNULL(M2,-1)>0 AND ISNULL(M3,-1)>0
AND (M1/M2)+M3>=1 AND M1/qty_free>=.5
GROUP BY item_id, qty_free, M3,M2,M1

I am quite confident this will eliminate the divide by zero as it eliminates all of the zeroes from the query by using -1

ASKER CERTIFIED SOLUTION

tips54

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial