Return-Path: Received: from newsfeed.cts.com (newsfeed.cts.com [209.68.248.164]) by naude.co.za (8.11.2/8.11.2) with SMTP id g4TNwFS08846 for ; Wed, 29 May 2002 19:58:15 -0400 Received: from fatcity.UUCP (uucp@localhost) by newsfeed.cts.com (8.9.3/8.9.3) with UUCP id IAA05289; Wed, 29 May 2002 08:55:10 -0700 (PDT) Received: by fatcity.com (26-Feb-2001/v1.0g-b71/bab) via UUCP id 0046E33E; Wed, 29 May 2002 07:18:32 -0800 Message-ID: Date: Wed, 29 May 2002 07:18:32 -0800 To: Multiple recipients of list ORACLE-L X-Comment: Oracle RDBMS Community Forum X-Sender: "Whittle Jerome Contr NCI" Sender: root@fatcity.com Reply-To: ORACLE-L@fatcity.com Errors-To: ML-ERRORS@fatcity.com From: "Whittle Jerome Contr NCI" Subject: RE: ora-918 Organization: Fat City Network Services, San Diego, California X-ListServer: v1.0g, build 71; ListGuru (c) 1996-2001 Bruce A. Bergman Precedence: bulk Mime-Version: 1.0 Content-Type: multipart/alternative; boundary="----_=_NextPart_001_01C20719.2A358928" ------_=_NextPart_001_01C20719.2A358928 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: quoted-printable 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@scott.af.mil 618-622-4145 > -----Original Message----- > From: Yechiel Adar [SMTP:adar76@inter.net.il] >=20 > Hello list >=20 > 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 =3D = 2) > and acct in (select acct from tab1 where balance > 7000 and month =3D = 3) > and acct in (select acct from tab1 where balance > 7000 and month =3D = 4) > group by acct; >=20 > Now the query runs OK with only one sub query. > Oracle 8.1.6.3.4 on NT. >=20 > I already replaced the three IN with 3 =3D select count() where month = in > (2,3,4) > and it works, but I would like to know why the original didn't. >=20 > Yechiel Adar > Mehish >=20 ------_=_NextPart_001_01C20719.2A358928 Content-Type: text/html; charset="us-ascii" Content-Transfer-Encoding: quoted-printable 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@scott.af.mil
618-622-4145

    -----Original = Message-----
    From:   Yechiel Adar [SMTP:adar76@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 =3D = 2)
    and acct in = (select acct from tab1 where balance > 7000 and month =3D = 3)
    and acct in = (select acct from tab1 where balance > 7000 and month =3D = 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 =3D 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

------_=_NextPart_001_01C20719.2A358928-- -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Whittle Jerome Contr NCI INET: Jerome.Whittle@scott.af.mil 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@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).