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

    Handle divide by zero error in SQL

    Robert Angel
      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.