Re: ora-918
Title: RE: ora-918 Thank 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 AdarMehish - 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. [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
ora-918
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: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
RE: ora-918
Title: 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. [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
RE: ora-918
Title: 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. [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
