ORA-00904: invalid identifier in subquery

oracle, sql

Solution

If you go by basics, a CORRELATED `Subquery` has access to the correlated table.. But When there's a INNER `subquery` , the `INNER Query` will be attempted to execute first ... So the other table in conditions cannot be acccessed, as they're not available at that point of time. Shortcut to understand this is.. as mentioned in the other answer..

SELECT A.* FROM TABLE A
WHERE EXISTS
 (SELECT 'X' FROM TABLE B WHERE B.ID = A.ID)

Now, The Correlated Subquery can access A.

select * from ENTITY_OWNERSHIP EO
where 
EXISTS
(
      select USER_ID 
      from ENTITY_OWNERSHIP 
      where ENTITY_OWNERSHIP.ENTITY_ID = EO.ENTITY_ID

      intersect

      select distinct group_id
      from USERS.GROUPS 
      start with GROUP_ID in (select GROUP_ID 
                               from USERS.LK_GROUPS
                             where USER_ID=10001)
      connect by prior PARENTGROUP_ID=GROUP_ID
)

Problem

Original source