title: Reference parent query column in subquery (Oracle)
body: <p>How can I reference a column outside of a subquery using Oracle?  I specifically need to use it in the WHERE statement of the subquery.</p>
  
  <p>Basically I have this:</p>
  
  <pre><code>SELECT Item.ItemNo, Item.Group
  FROM Item
    LEFT OUTER JOIN (SELECT Attribute.Group, COUNT(1) CT 
                     FROM Attribute 
                     WHERE Attribute.ItemNo=12345) A ON A.Group = Item.Group
  WHERE Item.ItemNo=12345
  </code></pre>
  
  <p>I'd like to change <code>WHERE Attribute.ItemNo=12345</code> to <code>WHERE Attribute.ItemNo=Item.ItemNo</code> in the subquery, but I can't figure out if this is possible.  I keep getting "ORA-00904: 'Item'.'ItemNo': Invalid Identifier"</p>
  
  <p><strong>EDIT:</strong></p>
  
  <p>Ok, this is why I need this kind of structure:</p>
  
  <p>I want to be able to get a count of the "Error" records (where the item is missing a value) and the "OK" records (where the item has a value).  </p>
  
  <p>The way I have set it up in the fiddle returns the correct data.  I think I might just end up filling in the value in each of the subqueries, since this would probably be the easiest way. Sorry if my data structures are a little convoluted.  I can explain if need be.</p>
  
  <p>My tables are:</p>
  
  <pre><code>create table itemcountry(
    itemno number,
    country nchar(3),
    imgroup varchar2(10),
    imtariff varchar2(20),
    exgroup varchar2(10),
    extariff varchar2(20) );
  
  create table itemattribute(
    attributeid varchar2(10),
    tariffgroup varchar2(10),
    tariffno varchar2(10) );
  
  create table icav(
    itemno number,
    attributeid varchar2(10),
    value varchar2(10) );
  </code></pre>
  
  <p>and my query so far is:</p>
  
  <pre><code>select itemno, country, imgroup, imtariff, im.error "imerror", im.ok "imok", exgroup, extariff, ex.error "exerror", ex.ok "exok"
  from itemcountry
    left outer join (select sum(case when icav.itemno is null then 1 else 0 end) error, sum(case when icav.itemno is not null then 1 else 0 end) ok, tariffgroup, tariffno
                     from itemattribute ia 
                       left outer join icav on ia.attributeid=icav.attributeid 
                     where (icav.itemno=12345 or icav.itemno is null) 
                     group by tariffgroup, tariffno) im on im.tariffgroup=imgroup and imtariff=im.tariffno
    left outer join (select sum(case when icav.itemno is null then 1 else 0 end) error, sum(case when icav.itemno is not null then 1 else 0 end) ok, tariffgroup, tariffno
                     from itemattribute ia 
                       left outer join icav on ia.attributeid=icav.attributeid 
                     where (icav.itemno=12345 or icav.itemno is null) 
                     group by tariffgroup, tariffno) ex on ex.tariffgroup=exgroup and extariff=ex.tariffno
  where itemno=12345;
  </code></pre>
  
  <p>It's also set up in a <a href="http://sqlfiddle.com/#!4/424e7/7" rel="noreferrer">SQL Fiddle</a>.</p>