If you want to avoid division by 0 issue, use NULLIF function to substitute zero in denominator with NULL.
For example, the following query fails:
SQL> SELECT 100/(SELECT COUNT(*) FROM emp WHERE deptno=40) 2 FROM dual; SELECT 100/(SELECT COUNT(*) FROM emp WHERE deptno=40) * ERROR at line 1: ORA-01476: divisor is equal to zero
To fix it, use NULLIF and the result will be NULL instead of error:
SELECT 100/NULLIF((SELECT COUNT(*) FROM emp WHERE deptno=40),0) expr FROM dual;
My Oracle Group on Facebook:
If you like this post, you may want to join my new Oracle group on Facebook: https://www.facebook.com/groups/sqlpatterns/
Would you like to read about many more tricks and puzzles?
For more tricks and cool techniques check my book “Oracle SQL Tricks and Workarounds” for instructions.