nullif()

Have you ever written SQL that had to protect against a “divide by zero” error ?

The way I used to do this was typically to introduce a decode() function to produce some vaguely appropriate result if the divisor were zero; but there is a tidier option that appeared a few years ago. For example:

select
	sql_id, 
	executions, 
	rows_processed, 
	rows_processed/(executions) rows_per_exec
from
	v$sql
where
	cpu_time > 1000000
;

ERROR:
ORA-01476: divisor is equal to zero

select
	sql_id,
	executions,
	rows_processed,
	rows_processed/nullif(executions,0) rows_per_exec
from
	v$sql
where
	cpu_time > 1000000
;

SQL_ID        EXECUTIONS ROWS_PROCESSED ROWS_PER_EXEC
------------- ---------- -------------- -------------
cn1gtsav2d5jh        653            653             1
d92h3rjp0y217         18             18             1
78m9ryygp65v5        856            856             1
bcvpx27d43v6s          0              3
63fyqfhnd7u5k         41            134    3.26829268

To avoid the error I’ve used the nullif() function – it’s easy to read: the function returns null if the first parameter equals the second parameter (otherwise it returns the first parameter). So in my example my divisor returns null if executions is zero. (And any arithmetic involving null returns null – which is something I’d generally rather see than a gap-filling “silly number”.)