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


      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

Original text of this message