ORA-00904:子查询中的标识符无效 [英] ORA-00904: invalid identifier in subquery

问题描述

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

Why the query below doesn't work in 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时，它就可以工作.

It produces "ORA-00904: "EO"."ENTITY_ID": invalid identifier". However when I replace EO.ENTITY_ID with exact value, for example, 10181 then it works.

更新: 完整的查询如下:

UPDATE: The full query looks like:

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

推荐答案

如果按基础进行操作，则 CORRELATED Subquery可以访问相关表. INNER subquery，将首先尝试执行INNER Query.因此，在该条件下无法访问其他表，因为此时该表不可用. 理解这的捷径是..如其他答案中所述.

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)

现在，相关子查询可以访问A.

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
)

这篇关于ORA-00904:子查询中的标识符无效的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！