ORA-01476: divisor is equal to zero

ORA-01476 is a very general error, and it comes when we try to divide any number by 0. In mathematics, DIVIDEND/0 has no meaning. Or we can simply say division by zero is undefined. In Oracle database when we try to divide by 0, Oracle Database throws an exception - "ORA-01476: divisor is equal to zero". For Example:

nimish@garg> WITH T AS (
  2   SELECT 1 DIVIDEND, 0 DIVISOR FROM DUAL
  3  )
  4  SELECT DIVIDEND/DIVISOR AS QUOTIENT FROM T;
SELECT DIVIDEND/DIVISOR AS QUOTIENT FROM T
               *
ERROR at line 4:
ORA-01476: divisor is equal to zero

Now the question is how to properly handle ORA-01476 properly in SQL, and return NULL (undefined) in such cases. I use following very simple methods to avoid ORA-01476.

1. Case When Then
Here we simply return NULL is DIVISOR is ZERO, otherwise we divide.

nimish@garg> WITH T AS (
  2   SELECT 1 DIVIDEND, 0 DIVISOR FROM DUAL
  3  )
  4  SELECT
  5     CASE WHEN DIVISOR = 0
  6     THEN NULL
  7     ELSE DIVIDEND/DIVISOR
  8     END QUOTIENT
  9  FROM T;

  QUOTIENT
----------

2. NULLIF(expr1, expr2) - I prefer this over CASE WHEN THEN approach

NULLIF compares expr1 and expr2. If they are equal, then the function returns NULL. If they are not equal, then the function returns EXPR1. In Oracle Database any mathematical operation involving NULL is evaluated to NULL - DIVIDEND/NULL = NULL

nimish@garg> WITH T AS (
  2   SELECT 1 DIVIDEND, 0 DIVISOR FROM DUAL
  3  )
  4  SELECT
  5     DIVIDEND/NULLIF(DIVISOR ,0) QUOTIENT
  6  FROM T;

  QUOTIENT
----------

Related Posts:
- ORA-06502: PL/SQL: numeric or value errorstring
- ORA-01489: result of string concatenation is too long
- ORA-01830 date format picture ends before converting entire input string
- PLS-00172: string literal too long
- ORA-01722: invalid number
- ORA-01439: column to be modified must be empty to change datatype
- ORA-01403: no data found