Simple SQL Question - Alias in Subquery 1

[Custom24]

Hi
I know this is a really elementary question, but I keep getting caught out by it.

I've got one view where it lists some stuff

SQL> select * from v_ins_ents_oustanding;

INSID INSNAME OUTSTANDING
---------- ------------------------------ -----------
101 MS8 17
102 MS9 0

I've got another view which contains some more information
SQL> select insid from v_monitored_procs;

INSID
----------
102
102

What I'd like to do is make a third view, which essentially contains v_ins_ents_oustanding, but with an extra column to count how many times this instrument appears in the other view.

Seems simple enough, but when I run this query, I get the oracle error ORA-00904 - invalid column name, with 'ThisINSID' highlighted in the subquery

select v.insid ThisINSID, cp.ProcsOutstanding
from V_INS_ENTS_OUSTANDING v,
(select count(*) ProcsOutstanding from v_monitored_procs vmp where vmp.INSID = ThisINSID) cp;

How then should I do this?

Thanks Mark

 

[malleykr]

Try something like this:

select v.insid, cp.ProcsOutstanding
from   V_INS_ENTS_OUTSTANDING v,
       (select count(*) ProcsOutstanding, vmp.insid
        from   v_monitored_procs vmp
        group by vmp.insid) cp
where  v.insid = cp.insid;

Good luck.

 

[jimbopalmer]

select v.insid, v.insname, v.outstanding, count(vmp.insid)
from V_INS_ENTS_OUSTANDING v, v_monitored_procs vmp
where vmp.INSID(+) = v.INSID
group by v.insid,v.insname, v.outstanding;

from a performance standpoint views of views is tough for the optimizer to do well on. I tried to remain child-like, all I acheived was childish.

 

[Custom24]

thanks guys - both of these work, though jimbopalmer's is faster.
Mark