ORA-00918 : column ambiguously defined
ORA-00918 also referred to as an ORA 918 is a common issue/error that occurs during the development of queries through joins of multiple tables and other issues listed below. Most of the time an Ora-00918 occurs due to the fact that a column with the same name exists in both tables. Another not so well documented reason for this issue to occur is the initial definition of views using a "select *" followed by addition of columns to the underlying table.
Issue 1: ORA-00918 is a common error. Nothing to worry here. It happens when a column name used in a query exists in more than one table during a join operation. So, this reference leads to the ambiguity causing oracle to throw an ORA-00918 error.
Issue 2: There are also reports of ORA-00918 error occuring when "full outer join" ANSI Syntax is used.
Issue 3: When new columns are added to tables in a schema and the view on top of the table is created without specific definition, ie, create view as select * from table, ora-00918 is known to occur. This is not well documented by oracle, but its a best practice to create views using a full definition instead of select *.
For example, you are joining a table emp and table dept on deptid. Lets assume that the deptid column exists in both tables. Look at the example below for more info on when an ORA-00918 error occurs and how to fix it.
ORA-00918 Error can occur when you write the following query.. the syntax creating the ORA-00918 is highlighted in red and the fix is highlighted in green.
select empid, deptid, name from emp e, dept d where e.deptid =d.deptid;
select empid, d.deptid, name from emp e, dept d where e.deptid = d.deptid;
ORA-00918: column ambiguously defined : Notes
Cause: A column name used in a join exists in more than one table and is thus referenced ambiguously. In a join, any column name that occurs in more than one of the tables must be prefixed by its table name when referenced. The column should be referenced as TABLE.COLUMN or TABLE_ALIAS.COLUMN. For example, if tables EMP and DEPT are being joined and both contain the column DEPTNO, then all references to DEPTNO should be prefixed with the table name, as in EMP.DEPTNO or E.DEPTNO.
Action: Prefix references to column names that exist in multiple tables with either the table name or a table alias and a period (.), as in the examples above.
Last edited by oracle-dba; 05-14-2011 at 04:28 AM.