Reference parent query column in subquery (Oracle)

Question

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.

Basically I have this:

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

I'd like to change WHERE Attribute.ItemNo=12345 to WHERE Attribute.ItemNo=Item.ItemNo in the subquery, but I can't figure out if this is possible. I keep getting "ORA-00904: 'Item'.'ItemNo': Invalid Identifier"

EDIT:

Ok, this is why I need this kind of structure:

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).

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.

My tables are:

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) );

and my query so far is:

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;

It's also set up in a SQL Fiddle.

Answer 1

You can do it in a sub-query but not in a join. In your case I don't see any need to. You can put it in the join condition.

select i.itemno, i.group
  from item i
  left outer join ( select group, itemno
                      from attribute b
                     group by group itemno ) a
    on a.group = i.group 
   and i.itemno = a.itemno
 where i.itemno = 12345

The optimizer is built to deal with this sort of situation so utilise it!

I've changed the count(1) to a group by as you need to group by all columns that aren't aggregated.

I'm assuming that your actual query is more complicated than this as with the columns you're selecting this is probably equivilent to

select itemno, group
  from item
 where itemno = 12345

You could also write your sub-query with an analytic function instead. Something like count(*) over ( partition by group).

As an aside using a keyword as a column name, in this case group is A Bad Idea ^TM. It can cause a lot of confusion. As you can see from the code above you have a lot of groups in there.

---

So, based on your SQL-Fiddle, which I've added to the question I think you're looking for something like the following, which doesn't look much better. I suspect, given time, I could make it simpler. On another side note explicitly lower casing queries is never worth the hassle it causes. I've followed your naming convention though.

with sub_query as (
 select count(*) - count(icav.itemno) as error
      , count(icav.itemno) as ok
      , min(itemno) over () as itemno
      , tariffgroup
      , tariffno
   from itemattribute ia 
   left outer join icav 
     on ia.attributeid = icav.attributeid 
  group by icav.itemno
         , tariffgroup
         , tariffno
  ) 
    select ic.itemno, ic.country, ic.imgroup, ic.imtariff
         , sum(im.error) as "imerror", sum(im.ok) as "imok"
         , ic.exgroup, ic.extariff
         , sum(ex.error) as "exerror", sum(ex.ok) as "exok"
      from itemcountry ic
      left outer join sub_query im 
        on ic.imgroup = im.tariffgroup
       and ic.imtariff = im.tariffno
       and ic.itemno = im.itemno
      left outer join sub_query ex 
        on ic.exgroup = ex.tariffgroup
       and ic.extariff = ex.tariffno
       and ic.itemno = ex.itemno
     where ic.itemno = 12345
     group by ic.itemno, ic.country
            , ic.imgroup, ic.imtariff
            , ic.exgroup, ic.extariff
           ;