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

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

The DECODE function is useful here. Assume you are going to divide x by
y. Then the following DECODE will show that result unless y=0, and then
it will show something else:

DECODE(y,0,'N/A',x/y)

You'll have to decide what the output will be if y is zero.

Either that, or consider eliminating those rows where y=0 by including:

WHERE y<>0

in your SQL statement.

HTH,
Brian

--
================================================== =================

Brian Peasland
dba@nospam.peasland.net
http://www.peasland.net

Remove the "nospam." from the email address to email me.


"I can give it to you cheap, quick, and good.
Now pick two out of the three" - Unknown

--
Posted via a free Usenet account from http://www.teranews.com

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
damorgan@x.washington.edu
(replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org

On Mar 8, 10:42 pm, DA Morgan <damor...@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...@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.

> The DECODE function is useful here. Assume you are going to divide x by
> y. Then the following DECODE will show that result unless y=0, and then
> it will show something else:
>
> DECODE(y,0,'N/A',x/y)
>
> You'll have to decide what the output will be if y is zero.
>
> Either that, or consider eliminating those rows where y=0 by including:
>
> WHERE y<>0
>
> in your SQL statement.
>
> HTH,
> Brian
>
> --
> ================================================== =================
>
> Brian Peasland
> d...@nospam.peasland.nethttp://www.peasland.net
>
> Remove the "nospam." from the email address to email me.
>
> "I can give it to you cheap, quick, and good.
> Now pick two out of the three" - Unknown
>
> --
> Posted via a free Usenet account fromhttp://www.teranews.com


Thanks brian, I will try your suggestion.

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

NVL will do it.
--
Daniel A. Morgan
University of Washington
damorgan@x.washington.edu
(replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org

On Mar 9, 10:13 pm, DA Morgan <damor...@psoug.org> wrote:
> > 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.
>
> NVL will do it.
> --
> Daniel A. Morgan
> University of Washington
> damor...@x.washington.edu
> (replace x with u to respond)
> Puget Sound Oracle Users Groupwww.psoug.org

Sorry I didn't follow up - To clarify what I've done...I created an
additional case statement for the case statement to eliminate the
divide by zero issue. After reading this I also used NVL to figure out
that solution, which also worked for me.