Re: How to eliminate divide by zero error

On Mar 8, 10:42 pm, DA Morgan <damor..._at_psoug.org> wrote:
> D wrote:
> > Hello - I'm looking to eliminate the divide by zero error using
> > coalesce on the following code. Not sure how to incorporate on
> > another
> > function. Please help!!
>
> > (round((case when(d.NUM_INC)=0 then 0 else (d.DUR_INC-
> > d.IN_WRAPUP_TIME-
> > d.DUR_HOLD)/d.NUM_INC end),0)/round(((case when(d.NUM_INC)=0 then 0
> > else (d.DUR_INC-d.IN_WRAPUP_TIME-d.DUR_HOLD)/d.NUM_INC end + case
> > when(d.NUM_INC)=0 then 0 else d.DUR_HOLD/d.NUM_INC end + case
> > when(d.NUM_INC)=0 then 0 else (d.OUT_WRAPUP_TIME+d.IN_WRAPUP_TIME)/
> > d.NUM_INC end)),0))
>
> No where in your code do I see the coalesce function. Nor have you
> indicated the version of Oracle. Nor have you identified what it is
> you expect Oracle to do if it encounters NULL.
>
> You have many choices from NVL to NVL2 to an exception handler etc.
>
> Without more information no help is possible.
> --
> Daniel A. Morgan
> University of Washington
> damor..._at_x.washington.edu
> (replace x with u to respond)
> Puget Sound Oracle Users Groupwww.psoug.org

Sorry Daniel,

         My attempts to incorporate the COALESCE function failed, therefore I omitted that from the code to eliminate confusion. I'm using Oracle 9 and I simply want Null to return '0' as a blank record in this case essentially means zero. Received on Fri Mar 09 2007 - 09:01:59 CST