ORA-00904：子查询中的标识符无效

Question

为什么下面的查询在oracle中不起作用？

select * from ENTITY_OWNERSHIP EO
where 
(select count (*) 
    from (
      select USER_ID 
      from ENTITY_OWNERSHIP 
      where ENTITY_OWNERSHIP.ENTITY_ID = EO.ENTITY_ID
    )
) > 0

产生“ ORA-00904：“ EO”。“
ENTITY_ID”：无效标识符”。但是，当我用精确值（例如10181）替换EO.ENTITY_ID时，它就可以工作。

更新：完整的查询如下所示：

select * from ENTITY_OWNERSHIP EO
where 
(select count (*) 
    from (
      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 
    )
) > 0

Answer 1

如果您是基础知识的话，则 CORRELATED Subquery可以访问相关表。但是，当有 INNER时 subquery，INNER Query将首先尝试执行该表…因此，处于条件中的其他表将无法使用，因为此时该表不可用时间点。如其他答案中所述，了解此内容的捷径是..

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

现在，相关子查询可以访问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
)