2 Replies Latest reply: Jun 12, 2013 7:14 PM by rp0428 RSS

    Sub-select query returns 'invalid identifier', but nested query returns rows


      I don't think this is a general SQL question.


      select * from People where person_id in (

           select person_id from Persons where name = 'Obama'     -- sub-query

      ) and age > 18;


      When I run the sub-query, I get:


      ORA-00904: "PERSON_ID": invalid identifier

      00904. 00000 - "%s: invalid identifier"



      Error at Line: 5 Column: 8


      This is because the table Persons don't have the field person_id.

      But when I run the whole nested query it returns all rows in People with AGE greater than 18.


      How come it succeeds when the sub-query obviously is erroneous?