Not able to refer column from inner query in hierarchical query?

Question

I am using hierarchical query to fetch out details from my table structure. I am referrring column from inner query in 'START WITH' condition, but its throwing unknown column error please check

Query :

select
  (select obj.name
    from (select LEVEL parentLevel, object_id id, name from temp_object START WITH object_id=sopi.OBJECT_ID CONNECT BY PRIOR parent_id = object_id) obj
            where parentLevel=4) "temp Order Name"
 from
  (SELECT OBJECT_ID
   FROM temp_params
   WHERE value = 'Add' AND object_id IN
                           (SELECT object_id
                            FROM temp_references
                            WHERE reference IN
                                  (SELECT object_id
                                   FROM temp_params
                                   WHERE list_id = 9133409) AND attt_id = '9133410')) sopi

Issue lies in highlighted area, to which I am planning to refer column from inner query.

i.e. START WITH object_id=sopi.OBJECT_ID is not working, please help

score 2 · Answer 1 · answered May 12 '16 at 12:13

The issue is that you're trying to reference a column from the grandparent query in the grandchild subquery. You can only reference column from outside the subquery if it's in the parent query, as per the documentation:

Oracle performs a correlated subquery when a nested subquery references a column from a table referred to a parent statement one level above the subquery.

Fortunately, you don't need the extra subquery level that you introduced; you should just be able to do:

select (select     name
        from       temp_object
        where      level = 4
        start with object_id = sopi.object_id
        connect by prior parent_id = object_id) obj "temp Order Name"
from   (select object_id
        from   temp_params
        where  value = 'Add'
        and    object_id in (select object_id
                             from   temp_references
                             where  reference in (select object_id
                                                  from   temp_params
                                                  where  list_id = 9133409)
                             and    attt_id = '9133410')) sopi

Ponder Stibbons · Answer 2 · 2016-05-12T12:11:18.650

I think that You have to move sopi subquery into start with clause and use in condition.

^{SQLFiddle demo}

select name
  from (select level parentlevel, object_id id, name
          from temp_object
         start with object_id in
                    (select object_id
                       from temp_params
                      where value = 'Add'
                        and object_id in
                            (select object_id
                               from temp_references
                              where reference in
                                    (select object_id
                                       from temp_params
                                      where list_id = 9133409)
                                and attt_id = '9133410'))
        connect by prior parent_id = object_id)
 where parentlevel = 4

Sample test data:

create table temp_params (object_id number(3), list_id number(8), value varchar2(3));
insert into temp_params values (1, 9133409, 'Add');
insert into temp_params values (2, 9133411, 'Add');
insert into temp_params values (3, 9133412, 'Add');

create table temp_references (object_id number(3), reference number(3), attt_id varchar2(8));
insert into temp_references values (2, 1, '9133410');
insert into temp_references values (3, 1, '9133410');

create table temp_object (object_id number(8), parent_id number(8), name varchar2(10));
insert into temp_object values ( 2, 21, 'Object 02');
insert into temp_object values (21, 22, 'Object 21');
insert into temp_object values (22, 23, 'Object 22');
insert into temp_object values (23, 24, 'Object 23');
insert into temp_object values (24, 25, 'Object 24');
insert into temp_object values ( 3, 31, 'Object 03');
insert into temp_object values (31, 32, 'Object 31');
insert into temp_object values (32, 33, 'Object 32');
insert into temp_object values (33, 34, 'Object 33');

Output:

NAME
----------
Object 23
Object 33

@rohansr002 If there are ids in the sopi subquery that don't have a level 4 row in the temp_object table, then Ponder's querywon't return the same as the original query - you'd have to do an outer join to get the "missing" rows. If it's always guaranteed that there will always be a row returned by the temp_object subquery, then this query will work fine — Boneist, May 12 '16 at 12:18

Not able to refer column from inner query in hierarchical query?

2 Answers2