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.