Yechiel,
"MONTH" is a reserved word in PL SQL and you have a field name "month". Could that be the problem?
Jerry Whittle
ACIFICS DBA
NCI Information Systems Inc.
[EMAIL PROTECTED]
618-622-4145
-----Original Message-----
From: Yechiel Adar [SMTP:[EMAIL PROTECTED]]
Hello list
I need help in solving ORA-918: Ambiguous field reference.
All the fields are schema.tablename.fieldname.
The SQL has 3 inline queries that are the same but with different
months in the where clause.
Simplified version:
select acct .....
from tab1, tab2, ....
where
.......
and acct in (select acct from tab1 where balance > 7000 and month = 2)
and acct in (select acct from tab1 where balance > 7000 and month = 3)
and acct in (select acct from tab1 where balance > 7000 and month = 4)
group by acct;
Now the query runs OK with only one sub query.
Oracle 8.1.6.3.4 on NT.
I already replaced the three IN with 3 = select count() where month in
(2,3,4)
and it works, but I would like to know why the original didn't.
Yechiel Adar
Mehish