oracle ORA-00904: 子查询中的标识符无效
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/21580705/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
ORA-00904: invalid identifier in subquery
提问by ako
Why the query below doesn't work in oracle?
为什么下面的查询在 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
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.
它产生“ORA-00904：“EO”。“ENTITY_ID”：无效标识符”。但是，当我用确切的值替换 EO.ENTITY_ID 时，例如 10181，它就可以工作了。
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
采纳答案by Maheswaran Ravisankar
If you go by basics, a CORRELATEDSubqueryhas access to the correlated table.. But When there's a INNERsubquery, the INNER Querywill 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..
如果你按照基础知识，一个CORRELATEDSubquery可以访问相关表..但是当有一个INNER 时subquery，INNER Query将首先尝试执行......所以条件中的另一个表不能被访问，因为它们不可用时间点。理解这一点的捷径是..如另一个答案中所述..
SELECT A.* FROM TABLE A
WHERE EXISTS
(SELECT 'X' FROM TABLE B WHERE B.ID = A.ID)
Now, The Correlated Subquery can access A.
现在，相关子查询可以访问 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
)
回答by Gordon Linoff
I think you can do it with existsinstead:
我认为你可以用它来exists代替：
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
);
