Re: NULL in outer joins?

In article <siong.800556205_at_sfu.ca>, Siong Chan <siong_at_malibu.sfu.ca> wrote:
>Hi there!
>I have a quick question here that has been puzzling me for sometime.
>Say, we obtained nulls in outer joins...how do we replace the nulls
>in say, ORACLE Reports with 0???
>Example,
>To select monthly salary for all departments, even those departments with
>no employees:
SOLUTION:
To do this change sum(sal) to nvl(sum(sal),0) whenever the query encounters a null value it changes to 0.
>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
>and you get:
>---
>Siong H. Chan
>Jason Sound Industries Ltd.
>

Thanks
Mahesh Vallampati

Home:				   Office:
401, Stasney Street, #224	|  642, Texas A & M Headquarters Building
College Station, Texas 77840	|  College  Station, Texas 77840
PH : 409 846 3794 (H)		|  409 862 1070 (O)

e-mail address  : vallampa_at_ee.tamu.edu
WWW 		: http://tam2000.tamu.edu/~m0v5533/
Lab 		: 409 847 8609 

M.S. In EE
Unix System Administrator and Oracle DBA for TexasAMP Research in Distributed Systems Lab on Multi-Threaded Systems.

// In the Beginning there was Codd .... Received on Mon May 15 1995 - 00:00:00 CEST