oracle10g - sql not throwing invalid identifier -
i asking question because not getting error expect there should error. please me understand under circumstances possible. have query:
select foracid,acct_name, schm_code, schm_type tbaadm.gam acid in(select acid tbaadm.iar);
this query returning results without throwing error. expect invalid identifier because table tbaadm.iar not have field acid. when run:
select acid tbaadm.iar;
i get:
ora-00904: "acid": invalid identifier 00904. 00000 - "%s: invalid identifier" *cause: *action: error @ line: 772 column: 8
i using sqldeveloper , oracle 10g. kind of strange me. worth mention though there field in tbaadm.iar id , right query should be:
select foracid,acct_name, schm_code, schm_type tbaadm.gam acid in(select entity_id tbaadm.iar);
what going on here?
a subquery that's used in in
clause can reference columns outer query, because necessary in correlated subqueries. clause equivalent to:
where acid in (select tbaadm.gam.acid tbaadm.iar)
an example of correlated subquery shows why necessary is:
select * outer_table somefield = (select someotherfield inner_table inner_table.id = outer_table.inner_id)
this more common use, field outer table used in where
clause of subquery. sql isn't picky field outer query used. can used anywhere in subquery expression permitted, includes select
clause.
Comments
Post a Comment