SQL - Error 'ORA-00918: column ambiguously defined' in SQL Developer

Question

So I'm having some issues producing a query with multiple temporary tables.

First I created the temporary tables.

CREATE GLOBAL TEMPORARY TABLE DIDV (DID VARCHAR(50))

INSERT INTO DIDV VALUES ('8090630909077434001');
INSERT INTO DIDV VALUES ('7471990179373590001');
INSERT INTO DIDV VALUES ('7605256906013877001');
INSERT INTO DIDV VALUES ('1604387368720407001');
INSERT INTO DIDV VALUES ('3512739534818409001');

CREATE GLOBAL TEMPORARY TABLE KEYW (KEW VARCHAR(50))

INSERT INTO KEYW VALUES ('%Corporate Insurance Pol%');
INSERT INTO KEYW VALUES ('%Tax Return%');
INSERT INTO KEYW VALUES ('%Audit%');
INSERT INTO KEYW VALUES ('%Corporate Governance%');
INSERT INTO KEYW VALUES ('%Board%');
INSERT INTO KEYW VALUES ('%Shareholder%');

Then I write my query as follows. It seems like a simple query. However, I get the error 'ORA-00918: column ambiguously defined'. I don't know if I can do a join of the 2 temporary tables, especially they do not have any relationship. I just want to query result where the unique id's are in the DIDV table and where the title and comment columns do not contain the KEW fields.

SELECT TO_CHAR(DID) DID, TO_CHAR(DROOTPARENTID) PARENTID, DBARCODE, 
DDOCTITLE, XCOMMENTS, XDIVISION, DEXTOBJECTTYPE
FROM PSA.URM_EXTITEMS_PSA, DIDV, KEYW
WHERE PSA.URM_EXTITEMS_PSA.DID = DIDV.DID 
AND PSA.URM_EXTITEMS_PSA.DDOCTITLE NOT LIKE KEYW.KEW 
AND PSA.URM_EXTITEMS_PSA.XCOMMENTS NOT LIKE KEYW.KEW;

Any help understanding this is appreciated.

Please let me know if any clarification is needed.

DID in the select list needs a table alias. Also, learn to use proper explicit JOIN syntax. — Gordon Linoff

krokodilko krokodilko · Accepted Answer · 2016-02-08T21:29:25

A table DIDV contains a column named DID
A table URM_EXTITEMS_PSA also contains a column named DID (I know this because a where condition contains a clause WHERE PSA.URM_EXTITEMS_PSA.DID = DIDV.DID ).

A select statement in your query contains an expression referencing the column DID:

SELECT TO_CHAR(DID) ....

Oracle doesn't really know (I don't know too) which DID do you want to get in a result - the 'DID' column which comes from DIDV table or the DID column which comes from URM_EXTITEMS_PSA table. Therefore Oracle throws an error ORA-00918: column ambiguously defined.

Just append an aliast to this DIV column in the select clause, either DIDV or URM_EXTITEMS_PSA, in this way:

SELECT TO_CHAR(DIDV.DID) ....

I know that WHERE clause contains PSA.URM_EXTITEMS_PSA.DID = DIDV.DID and actually both DIDs are equal, so Oracle should be smart enaugh to say Oh, yes, he wrote PSA.URM_EXTITEMS_PSA.DID = DIDV.DID, so I can pick DID from any table ....
Unfortunatelu Oracle is not so smart (maybe they improve this in a new version ?), and for now you must append an alias or a table name.

SQL - Error 'ORA-00918: column ambiguously defined' in SQL Developer

1 Answers