RE: NULL in outer joins?
From: Keith B. McKendry <Keith_McKendry_at_csg.mot.com>
Date: 1995/05/18
Message-ID: <9505180849.ZM8567_at_MFGMPC>#1/1
Date: 1995/05/18
Message-ID: <9505180849.ZM8567_at_MFGMPC>#1/1
siong_at_malibu.sfu.ca (Siong Chan) wrote:
> Example,
> To select monthly salary for all departments, even those departments
> with
> no employees:
> select dept.deptno, dname, SUM(sal) "Monthly Salary"
> from emp, dept
> where emp.deptno(+) = dept.deptno
> group by dept.deptno, dname
> order by dept.deptno
Try:
Example,
To select monthly salary for all departments, even those departments
with
no employees:
select dept.deptno, dname, nvl(SUM(nvl(sal,0)),0) "Monthly Salary"
from emp, dept
where emp.deptno(+) = dept.deptno
group by dept.deptno, dname
order by dept.deptno
This also covers the case where an employee has null for salary.
-- "All opinions are my own and do not reflect those of my employer nor those of my clients," the consultant disclaimed. Keith McKendry | 73071,3233_at_compuserve.com SSC, Inc. | | Please ignore address in header and submit responses to compuserve address.Received on Thu May 18 1995 - 00:00:00 CEST