January 2, 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:

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