Problem
Ratio is very useful variable. For example, the ratio between credit card account balance and credit limit is a good measurement of the default risk. However, when calculating ratio, it the denominator is zero, Oracle raises an error,ORA-01476: divisor is equal to zero, and stops the query. This is inconvenient as shown below.
SQL> select * from tbl_account;
CUSTOMER_ID ACCT_BALANCE CREDIT_LIMIT
----------- ------------ ------------
1 2000 5000
2 0 0
3 6000 12000
SQL> select a.*, ACCT_BALANCE/CREDIT_LIMIT r from tbl_account a;
ERROR:
ORA-01476: divisor is equal to zero
no rows selected
Solution
One solution to calculating ratio or division is to build a function that returns NULL when the denominator is zero and normal value when it is not zero. The script below builds such a function div(num1, num2) that calculates num1/num2. It will return NULL when num2 is zero and continue the execution.
create or replace FUNCTION div (
num1 number,
num2 number
)
RETURN NUMBER IS
v_number NUMBER;
BEGIN
BEGIN
v_number:= num1/num2 ;
EXCEPTION
WHEN OTHERS THEN
RETURN NULL;
END;
RETURN v_number;
END div;
/
After we run the above script to build function div(), we can use it to replace division "/".
SQL> select a.*, div(ACCT_BALANCE,CREDIT_LIMIT) r from tbl_account a;
CUSTOMER_ID ACCT_BALANCE CREDIT_LIMIT R
----------- ------------ ------------ ----------
1 2000 5000 .4
2 0 0
3 6000 12000 .5
The following are other outputs of using div()function.
SQL> select div(0,0) r from dual;
R
----------
SQL> select div(NULL,0) r from dual;
R
----------
SQL> select div(NULL,NULL) r from dual;
R
----------
