How do you resolve ORA 00918 column ambiguously defined?

In IBM Engineering Workflow Management (EWM), a work item query fails to execute due to ORA-00918 "column ambiguously defined" error

Symptom

Steps to reproduce:

1. Create a work item query
2. Do not specify any conditions
3. On the 'column display' tab, add only the "ID" attribute
4. Add two sorting columns: "ID" and "planned for"
5. Run the query

The following error is returned:

ORA-00918 "column ambiguously defined"

Notes:

• Both 'ID' and 'Planned For' are stored in database columns named 'ID'.
• The work item query generates a database query with the following ORDER BY clause:

  order by t1.ID asc, NLSSORT(t4.ID, 'NLS_SORT=GENERIC_M') asc fetch first 1000 rows only

• The error can be reproduced with any combinations of the sorting columns that have the same database column name. For example, instead of 'ID' and 'Planned For', you can sort on 'Created by' and Owned by'.

Cause

Unsupported Oracle database version is used with EWM 7.0.2.

The problem is reproducible in EWM 7.0.2 running with Oracle 12c Release 1 and 12.2.0.1.0 JDBC driver.

Environment

EWM 7.0.2

Oracle 12c Release 1 with 12.2.0.1.0 driver

Diagnosing The Problem

1. Reproduce the problem
2. Check ccm.log for the following error:

2022-01-06 08:50:44,207 [WebContainer : 13 @@ 08:50 <UserID> <Client details> /ccm/service/com.ibm.team.workitem.common.internal.rest.IQueryRestService/getResultSet] WARN .team.repository.servlet.AbstractTeamServerServlet [TID: <TID>] - CRJAZ1163E InternalRepositoryException error occurred while processing a POST request for com.ibm.team.workitem.common.internal.rest.IQueryRestService.postGetResultSet(). CRJAZ1170I The request was made by user "<UserId>" from "<Client IP>".CRJAZ1166I The stack trace hash is <Hash Value>.
com.ibm.team.repository.common.InternalRepositoryException: CRJAZ0368E A database query could not run on the server.

...

Caused by: Error : 918, Position : 0, Sql = <Query>, Error Msg = ORA-00918: column ambiguously defined

The failed database query contains an 'order by' clause that has two or more columns with the same name. The following example shows t1.ID and t4.ID.

order by t1.ID asc, NLSSORT(t4.ID, 'NLS_SORT=GENERIC_M') asc fetch first 1000 rows only

Resolving The Problem

• Ensure that your database version is supported on the version of IBM Engineering Lifecycle Management (ELM) products that you are running.
• Ensure that the JDBC driver is compatible with database version

Related Information

ELM 7.0.2 System Requirement documentation

Document Location

Worldwide

[{"Type":"MASTER","Line of Business":{"code":"LOB59","label":"Sustainability Software"},"Business Unit":{"code":"BU059","label":"IBM Software w\/o TPS"},"Product":{"code":"SSUC3U","label":"IBM Engineering Workflow Management"},"ARM Category":[{"code":"a8m50000000Cjd6AAC","label":"Workflow Management->Work Items"}],"ARM Case Number":"TS007861042","Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"7.0.2"}]

ORA-00918: column ambiguously defined error occurs when a column name in a join exists in more than one table and is thus referenced ambiguously. The ORA 00918 column ambiguously defined error occurs when attempting to join two or more tables with the same name across columns. This column name is referred as an ambiguous reference. If a column with the same name exists in two or more tables, the column name should be prefixed with the table name in joins. Otherwise, the column is identified ambiguously in the join, and the sql query is unable to determine the column name from the tables. In this scenario, the error message ORA-00918: column ambiguously defined will be shown.

The joins in the sql query combine all of the columns from two or more tables. If a column name is used in two or more tables, the column name is ambiguously recognized in the SQL join. Oracle will give an error ORA-00918: column ambiguously defined, if the column name is used to refer. The reference to the column name should be distinguished in some way. There are several methods for uniquely identifying the column names in the join.

When the ORA-00918 error occur

If two or more tables with the same column name are created and joined in a sql query, the column name may be recognized ambiguously. Because the column name is available in all of the join tables, Oracle could not match with any one table to get the data. The error ORA-00918: column ambiguously defined will be thrown in this scenario.

Problem

create table dept(
deptid number primary key,
deptname varchar2(100)
);

create table employee(
id number primary key,
name varchar2(100),
deptid number, foreign key(deptid) references dept(deptid)
);

select * from dept, employee where deptid=1;

Error

ORA-00918: column ambiguously defined
00918. 00000 -  "column ambiguously defined"
*Cause:    
*Action:
Error at Line: 16 Column: 36

Root Cause

If more than one table includes the same column name and refers to those columns in a join, the column name will be ambiguous. Oracle will search in the joined tables if you refer to the column name. If the same column name appears in two or more tables, the column name is identified ambiguously. With those tables, the join could not be performed. There is no way to distinguish the columns.

Solution 1

If the same column name appears in multiple tables and is referenced in a join, the column name becomes ambiguous. In sql joins, the column name is identified ambiguously. It is necessary to differentiate the columns in the joins. One method is to prefix the table name when referring it in joins. The table name is used to uniquely identify the column name.

Problem

create table dept(
deptid number primary key,
deptname varchar2(100)
);

create table employee(
id number primary key,
name varchar2(100),
deptid number, foreign key(deptid) references dept(deptid)
);

select * from dept, employee where deptid=1;

ORA-00918: column ambiguously defined
00918. 00000 -  "column ambiguously defined"

Solution

select * from dept, employee where dept.deptid=1;

Solution 2

The column name becomes ambiguous if it occurs in many tables and is referenced in a join. The column name is ambiguously recognized in sql joins. In order to separate the columns in the joins, they must be differentiated. If you use the same table in a sql join again, referencing the column by table name will fail. The table alias should be used to refer to the column name in this situation.

Problem

create table employee(
id number primary key,
name varchar2(100),
managerid number, foreign key(managerid) references employee(id)
);

select * from employee, employee where id=managerid;

ORA-00918: column ambiguously defined
00918. 00000 -  "column ambiguously defined"

Solution

select * from employee mgr, employee emp where mgr.id=emp.managerid;

Solution 3

When a column name appears in many tables and is referenced in a join, it becomes confusing. In sql joins, the column name is recognized ambiguously. The columns in the joins must be distinct in order to be separated. You may use a select query to change the column names before using them in joins. The select query will provide a list of unique column names to which you may refer. In the example below a select query is used in the joins.

How do you resolve the Oracle error Ora 00918 column ambiguously defined?

What does it mean by column ambiguously defined?

How to fix invalid identifier in Oracle?

How does left join work in Oracle?