Re: NULL in outer joins?

Try,
select dept.deptno, dname, NVL(SUM(sal), 0) "Monthly Salary" from emp, dept
where emp.deptno(+) = dept.deptno
group by dept.deptno, dname
order by dept.deptno
/
Regards,
Nebojsa Nastic
Designer/2000 Ambassador
:

 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:
 

: 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:
 

: DEPTNO DNAME MONTHLY SALARY
: ----------- ------------ --------------
: 10 ACCOUNTING 8750
: 20 RESEARCH 10875
: 30 OPERATIONS <----Blank or NULL because no emp.
 

: How can I replace that blank or null with 0 in ORACLE REPORTS?
 

: I have tried a format trigger htat says, IF SUM(sal) IS NULL THEN...
: doesn't work. So....any ideas???
 

: Platform: ORACLE REPORTS 2.0.14
 

: Thanks in advance!!!

: ---
: Siong H. Chan
: Jason Sound Industries Ltd.
Received on Mon May 15 1995 - 00:00:00 CEST