Re: Divide by zero conditions in views and queries.

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: 1998/06/12
Message-ID: <35844ba4.11340526_at_192.86.155.100>#1/1

A copy of this was sent to sysdev_at_adpsystems.mb.ca (if that email address didn't require changing) On Fri, 12 Jun 1998 15:07:41 GMT, you wrote:

>Hi all,
>
>We are using Oracle 7.2.3.
>
>I have to create a view that does a group by,
>and also creates a number of percentages.
>
>ie.
>CREATE OR REPLACE VIEW .. AS
>select
>rep, time_period,
>sum(sales) GROSS_SALES,
>sum(commission_paid) COMMISSION,
>sum(commission_paid)/ sum(sales) AVG_COMMISSION_PERCENT
>from ...
>where ...
>group by rep, time_period
>

I think DECODE will do much better then NVL here. For example, I added a SALES column to my emp table and set sales = 5*comm (except for one row where I set sales to NULL) then

SQL> l
  1 select ename, sum(sales), sum(comm),   2 decode( sum(sales), NULL, NULL, 0, NULL, sum(comm)/sum(sales) )   3 from emp
  4* group by ename
SQL> / ENAME SUM(SALES) SUM(COMM) DECODE(SUM(SALES),NULL,NULL,0,NULL,SUM(C

---------- ---------- ---------- ----------------------------------------
ADAMS      (null)     (null)     (null)
ALLEN            4000        800 .2
BLAKE      (null)     (null)     (null)
BLOGGS     (null)     (null)     (null)
CLARK             500        100 .2
FORD       (null)     (null)     (null)
JAMES      (null)     (null)     (null)
JONES             500        100 .2
KING       (null)     (null)     (null)
MARTIN           7000       1400 .2
SCOTT            1005        201 .2
SMITH      (null)     (null)     (null)
TURNER              0          0 (null)
WARD             3000        600 .2

14 rows selected.

SQL> So, the decode will return NULL when sum(sales) is null or when sum(sales) is ZERO, else it will return sum(comm)/sum(sales)... Avoids the zero divides and all.

>
>
>Our ISP is really bad with the newsgroups.
>Emails to
>sysdev_at_adpsystems.mb.ca
>are greatly appreciated.
>
>
>Regards,
>
>Rodger
>
>
>
>
>
>
>-----== Posted via Deja News, The Leader in Internet Discussion ==-----
>http://www.dejanews.com/ Now offering spam-free web-based newsreading
 

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 Fri Jun 12 1998 - 00:00:00 CEST