Oracle-L: Re: ora-918

From: Yechiel Adar <adar76_at_inter.net.il>
Date: Thu, 30 May 2002 00:08:19 -0800
Message-ID: <F001.0046F2CA.20020530000819@fatcity.com>

RE: ora-918Thank Jerry, but month represents field named ia_validity_month. The sql in my message is just to give you an idea what the problematic sql looks like.

Yechiel Adar
Mehish

Original Message ----- From: Whittle Jerome Contr NCI To: Multiple recipients of list ORACLE-L Sent: Wednesday, May 29, 2002 5:18 PM Subject: RE: ora-918

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.
  jerome.whittle_at_scott.af.mil
  618-622-4145

-----Original Message-----
From: Yechiel Adar [SMTP:adar76_at_inter.net.il]

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 Thu May 30 2002 - 03:08:19 CDT