How to avoid DIVIDE BY ZERO error in an SQL query
Have you tried using NULLIF()
?
SELECT ( 100 / NULLIF( 0, 0 ) ) AS value ;
Oracle Doc
http://www.oracle-base.com/articles/9i/ANSIISOSQLSupport.php#NULLIFFunction
Another example
http://www.bennadel.com/blog/984-Using-NULLIF-To-Prevent-Divide-By-Zero-Errors-In-SQL.htm
If you want to ignore such records you can use a subquery
SELECT YEAR, period, round((1- rej_sum / recd_sum)*100, 0) FROM( SELECT YEAR, sum(rej_qty) rej_sum, sum(recd_qty) recd_sum FROM TAB_A WHERE sid = '200' AND sdid IN ('4750') AND ( ( YEAR ='2011' AND period IN('01_JAN') ) OR ( YEAR = '2010' AND period IN ('02_FEB','03_MAR','04_APR','05_MAY','06_JUN','07_JUL','08_AUG','09_SEP','10_OCT','11_NOV','12_DEC') ) ) group by year, period)WHERE recd_sum <> 0;
If you want to keep them and handle the division by zero issue, you can use decode or case
SELECT YEAR, period, DECODE(recd_qty, 0, NULL, round((1- sum(rej_qty) / sum(recd_qty))*100, 0))
round(ISNULL(((1- sum(rej_qty)) / NULLIF( (sum(recd_qty))*100), 0 )),0),0)
If you replace your division using NULLIF to set a NULL when there is divide by zero, then an ISNULL to replace the NULL with a 0 - or indeed whatever value you want it to.