ORA 960: Ambigous Column Naming?? in ODBC-sql statement

[dirkg]

Hi all,

I've made an append-query which appends selected data of a table located on an Oracle database to a an Access-table. I made the query in MSAccess and made an ODBC-link to the ORACLE-table. The ORACLE-table is called EKET and the Access-table is called Pre-EKET. The records I want to select are records for which the key is also present in the Access-table Pre-EKPO. So there's a link between the Oracle-Table and the Access-table. The strange thing is that I have executed already several queries like this and it worked without any problems. For this query however I get following error:
[ORACLE][ODBC ORACLE DRIVER][ORACLE OCI]ORA-00960: ambiguous column naming in select list (#960)

Can somebody help me with this one, it really drives me crazy. I tried everything: renaming the columns, changing the type, etc...

The sql-statement is the following:
INSERT INTO [Pre-EKET] ( EBELN, EBELP, ETENR )
SELECT DISTINCTROW [Pre-EKPO].EBELN, [Pre-EKPO].EBELP, SAPR3_EKET.ETENR
FROM SAPR3_EKET INNER JOIN [Pre-EKPO] ON (SAPR3_EKET.EBELP =
[Pre-EKPO].EBELP) AND (SAPR3_EKET.EBELN = [Pre-EKPO].EBELN);


All help is really welcome. Thank you very much in advance!

Greetings,

Dirk

dirk.news@yucom.be

 

[THoey]

That error usually occurs when you have like field names in two or more tables and try to write a query without specifically calling table.field name. Try adding the table name preface before each field, so:

INSERT INTO [Pre-EKET] ( EBELN, EBELP, ETENR )

becomes:

INSERT INTO [Pre-EKET] ( [Pre-EKET].EBELN, [Pre-EKET].EBELP, [Pre-EKET].ETENR )

etc...

Hope that helps...




Terry M. Hoey
th3856@txmail.sbc.com

Ever notice that by the time that you realize that you ran a truncate script on the wrong instance, it is too late to stop it?

 

[dirkg]

Hi Terry,

thanks for your tip but that didn't make any difference: I still got the same error. However now I added an extra key-field in the Access table and that seems to work...but how come? I'm even sure that this query worked before and before I ran another, similar query that worked. It is not the first time I have this problem: at first the query works and when I try to run it several days after I get this ORA 960 error...strange right? and very ennoying too!
So if anyone could give me more information on what might cause this, you're very welcome!


Greetings,

Dirk

 

[THoey]

By ambiguous, Oracle could be teeling you that multiple rows were returned that met your criteria. It may not know which record you were wanting to pull data from for your INSERT. By adding the other key field, maybe it got a distinct match.

As we all have learned, most of Oracle's error messages are, well, ambiguous....

Glad you got it working...


Terry M. Hoey
th3856@txmail.sbc.com

Ever notice that by the time that you realize that you ran a truncate script on the wrong instance, it is too late to stop it?