Re: NULL in outer joins?

siong_at_malibu.sfu.ca (Siong Chan) 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.
>

Check out SQL function NVL.

You're welcome. Received on Mon May 15 1995 - 00:00:00 CEST