When I execute the following query ,
It returns the values.
select * from TABLE_1 where FIELD1 in (
select FIELD1 from Table2 where FIELD2 = 'AAAAAAA' and FIELD3 = 'SSSSSS') and FIELD2 in (select FIELD2 from TABLE3
where FIELD3 in ('XXXXX', 'YYYYY', 'ZZZZZZ')) The problem is TABLE3 don't have FIELD2 it has only FIELD3. So if I run
select FIELD2 from TABLE3
where FIELD3 in ('XXXXX', 'YYYYY', 'ZZZZZZ')It shows FIELD2 is not available in TABLE3. But if I run the whole query, it gives the answer.
Can anyone explain how it is happening?
Your first query is resolving FIELD2 from TABLE_1.
select *
from table_1
where field1 in (
select field1 from table2 where field2 = 'AAAAAAA' and field3 = 'SSSSSS'
)
and field2 in (
select table_1.field2 from table3 where field3 in ( 'XXXXX', 'YYYYY', 'ZZZZZZ' )
)
To remove this kind of ambiguity, qualify your column names!
with t1 as (select 1 x from dual), t2 as (select 1 y from dual)
select * from t1
where x in (select x from t2);
X
1
with t1 as (select 1 x from dual), t2 as (select 1 y from dual)
select * from t1
where x in (select t1.x from t2);
X
1
with t1 as (select 1 x from dual), t2 as (select 1 y from dual)
select * from t1
where x in (select t2.x from t2);
SQL Error: ORA-00904: "T2"."X": invalid identifier