5 Replies Latest reply on Oct 11, 2010 10:31 AM by Robert Angel

# Handle divide by zero error in SQL

Hi,

a few posts back someone asked the best way to handle divide by zero.

Can I revisit this theme with, what is the best way to handle divide by error in SQL without repeating code...

What I have in mind is quite frequently divided by error appears as the result of a lengthy formula calculation - and trying to burying a lengthy a lengthy formula calculation in a case statement gives you an exceedingly long result, which is not great for code maintenance.

Wrapping the handling in a function is also not great for reasons of performance.

So is there any simple way to do the equivalent of (pseudo code)

select IFerr( actual / (RidiculouslyLongConvulutedFormulaHereWhichSometimesYieldsZeroValues), 0) from dual

Where IFerr works like NVL - except it yields 0 when the result is a number error, or the result otherwise?

thanks,

Robert.
• ###### 1. Re: Handle divide by zero error in SQL
Hi,
a few posts back someone asked the best way to handle divide by zero.
I didn't see that thread, but does it mention NULLIF function?

You can apply it to every denominator in your formula, so that it yields NULL in case it evaluates to 0.

For example,
``````with t as (
select 1 numerator,
0 denominator
from dual
)
select numerator/nullif(denominator,0) as div
from t;``````
• ###### 2. Re: Handle divide by zero error in SQL
odie_63 wrote:
Hi,
a few posts back someone asked the best way to handle divide by zero.
I didn't see that thread, but does it mention NULLIF function?

You can apply it to every denominator in your formula, so that it yields NULL in case it evaluates to 0.
Yes, it did mention nullifying the divisor.

Other than that it would be a case of capturing the exception, which can be done in a function and then returning null, or a default value if required.

Whatever way is chosen there's nothing automatic in Oracle to do it, it has to be captured and handled in some way in the code.
• ###### 3. Re: Handle divide by zero error in SQL
This yields an error not zero...

select nullif( 9 / 0 ,0) from dual

So I don't think that helps.

regards,

Robert.
• ###### 4. Re: Handle divide by zero error in SQL
This yields an error not zero...

select nullif( 9 / 0 ,0) from dual

So I don't think that helps.
Yes, sure, if you use it that way.