CODEWITHSUNDEEP
sqloracle-database
ako
ako

Reputation: 420

ORA-00904: invalid identifier in subquery

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

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

Upvotes: 4

Views: 16036

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269683

I think you can do it with exists instead:

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 
    );

Upvotes: 1

Maheswaran Ravisankar
Maheswaran Ravisankar

Reputation: 17920

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
)

Upvotes: 4

Related Questions