Re: subtracting variables in oracle

From: Spencer <spencerp_at_swbell.net>
Date: Sun, 28 Jan 2001 15:03:43 -0600
Message-ID: <IO%c6.60$Dk2.77224@nnrp3.sbc.net>

>
> create view t_v as
> select lastname, firstname, title, department,
> nvl(sum(decode(pay_type, 'Gross', amount, 0)),0) -
> nvl(sum(decode(pay_type, 'Bonus', amount, 0)),0) gross_no_bonus
> from yourtable
> group by lastname, firstname, title, department;
>

here is another way to accomplish close to the same thing...

create or replace view t_v
as
select lastname

     , firstname
     , title
     , department
     , SUM( amount * DECODE(pay_type,'Gross',1,'Bonus',-1,0) )

gross_no_bonus
  from yourtable
 group by lastname

        , firstname
        , title
        , department ;

by adding a multiplication operator, you only have to reference the columns amount and pay_type one time, use only one SUM function, and eliminate the NVL functions entirely. the only difference in the result will be that this could return a NULL for gross_no_bonus in cases where the other query would return a zero. adding a single NVL function to this query will generate the same result, although i think the NULL would be more appropriate than a zero. Received on Sun Jan 28 2001 - 15:03:43 CST