Oracle Scratchpad

January 2, 2011

nullif()

Filed under: NULL — Jonathan Lewis @ 7:14 pm GMT Jan 2,2011

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”.)

1 Comment »

  1. Nice! One of those little gems that make life easier.

    Comment by Stew Ashton — January 2, 2011 @ 9:29 pm GMT Jan 2,2011 | Reply


RSS feed for comments on this post. TrackBack URI

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

Theme: Rubric. Get a free blog at WordPress.com

Follow

Get every new post delivered to your Inbox.

Join 4,267 other followers