Search

Top 60 Oracle Blogs

Recent comments

January 2011

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

Analytic Functions – What is Wrong with this Statement?

January 1, 2011 I was a bit excited to see the chapter discussing analytic functions in the book “Pro Oracle SQL”, which has a rather extensive coverage of most of Oracle Database’s analytic functions (something that I have not seen from other SQL books).  That chapter is very well assembled, with easier to understand descriptions [...]

O-1 Visa

Congratulations to Mark Rittman of Ritmann Mead who emailed me last night to tell me that his application for an O1 visa (“aliens of exceptional ability”) to work in the USA had been granted.

There aren’t many of us around in the Oracle field (in a quick google search the I found just one other holder from the UK in a facebook entry for a Hyperion specialist) and Mark is certainly an appropriate addition to the select band.