Re: problem with view

You can't do it that way. Do it like this:

create view irlinev10 as

select b.realm,
       a.costctr,
       a.chart,
       a.account,
       a.subacct,
       sum(decode(c.approvedby,NULL,a.amount,0)) unapprovedamt,
       sum(decode(c.approvedby,NULL,0,a.amount)) approvedamt
from irline a,
     costctr b,
     itemreq c
where b.costctr = a.costctr and
      c.request = a.request
group by b.realm,
         a.costctr,
         a.chart,
         a.account,
         a.subacct

Ty O'Kelly wrote:

> i am having difficulty with the following view. oracle does not like
> the "NOT NULL" i have in the second decode statment. can any of you
> gurus tell me what i am doing wrong?
> create view irlinev10 as
> select b.realm,
> a.costctr,
> a.chart,
> a.account,
> a.subacct,
> sum(decode(c.approvedby,NULL,a.amount,0)) unapprovedamt,
> sum(decode(c.approvedby,NOT NULL,a.amount,0)) approvedamt
> from irline a,
> costctr b,
> itemreq c
> where b.costctr = a.costctr and
> c.request = a.request
> group by b.realm,
> a.costctr,
> a.chart,
> a.account,
> a.subacct
Received on Fri Jul 16 1999 - 09:23:39 CDT