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

tips54Asked:
Who is Participating?
 
tips54Connect With a Mentor Author Commented:
Ksparky,
the statement did not resolve the divide by zero error.
I resolved this some other way
0
 
peter57rCommented:
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.
0
 
tips54Author Commented:
thanks.
I added the parenthesis.  Can you think of any way to address the divide by zero error?
0
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
peter57rCommented:
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?
0
 
Christopher GordonSenior Developer AnalystCommented:
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
0
 
ksparkyCommented:
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
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.