Re: calculated fields and divide by zero

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Tue, 21 Jul 1998 18:55:33 GMT
Message-ID: <35bbe407.22243955@192.86.155.100>

A copy of this was sent to Kevin Skaalrud <kevin_skaalrud_at_neptune.serca.com> (if that email address didn't require changing) On Tue, 21 Jul 1998 10:15:58 -0700, you wrote:

>Thanks to Thomas Kyte for sovling my this year vs
>last year query.
>
>My next problem is this: when I try to calculate
>GP%, say, (sales-margin)/sales, if sales is zero
>oracle aborts the query with a divide by zero error.
>

select decode( sales, 0, NULL, (sales-margin)/sales )   from t

will return NULL if sales is ZERO, else returns the function...  

>Any thoughts on how to avoid this behaviour. I would
>prefer to see INF or 0 returned instead of abend on
>the query.
>
>Kevin Skaalrud
 

Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government
Herndon VA  

http://govt.us.oracle.com/ -- downloadable utilities  

---

Opinions are mine and do not necessarily reflect those of Oracle Corporation  

Anti-Anti Spam Msg: if you want an answer emailed to you, you have to make it easy to get email to you. Any bounced email will be treated the same way i treat SPAM-- I delete it. Received on Tue Jul 21 1998 - 13:55:33 CDT