ora-918

From: Yechiel Adar <adar76_at_inter.net.il>
Date: Wed, 29 May 2002 02:13:26 -0800
Message-ID: <F001.0046DC1F.20020529021326@fatcity.com>

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

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Yechiel Adar
  INET: adar76_at_inter.net.il

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L


(or the name of mailing list you want to be removed from).  You may

also send the HELP command for other information (like subscribing).

Received on Wed May 29 2002 - 05:13:26 CDT