Prevent Division by Zero with NULLIF function

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.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s