Ora-00918: Column Ambiguously Defined Error When Executing a Query (Doc ID 819962.1)

Last updated on AUGUST 04, 2017

Applies to:

Oracle TopLink - Version 11.1.1.0.1 to 11.1.1.0.2 [Release Oracle11g]
Information in this document applies to any platform.

Symptoms

Entity Object Model

Query Definition

ReadAllQuery query = new ReadAllQuery();
query.setReferenceClass(MaterialBO.class);
ExpressionBuilder expression = query.getExpressionBuilder();
query.setSelectionCriteria(expression.get("id").equal("0"));

query.addJoinedAttribute(expression.getAllowingNull("verpackung"));
query.addJoinedAttribute(expression.getAllowingNull("auftrag"));
query.addJoinedAttribute(expression.getAllowingNull("auftrag").getAllowingNull("verarbeiter"));

List<MaterialBO> result = (List<MaterialBO>) session.executeQuery(query);

Generated SQL Statement

SELECT
  t1.ID,
  t1.TKSIDENT,
  t1.FK_VERPACKUNG_MATERIAL,
  t1.FK_AUFTRAG_MATERIAL,
  t0.ID,
  t2.ID,
  t2.CLASSTYPE,
  t3.ID,
  t3.FK_VERARBEITER_VERATRAG,
  t4.ID
FROM
  AUFTRAG t3 LEFT OUTER JOIN KUNDE t4 ON (t4.ID =t3.FK_VERARBEITER_VERATRAG),
  MATERIAL t1 LEFT OUTER JOIN VERPACKUNG t0 ON (t0.ID=t1.FK_VERPACKUNG_MATERIAL)
    LEFT OUTER JOIN (ARBEITSPLAN t2 JOIN AUFTRAG t3 ON (t3.ID = t2.ID))
      ON (t2.ID = t1.FK_AUFTRAG_MATERIAL)
WHERE (t1.ID = ?)

Observed Behaviour

The following error occurs because "AUFTRAG t3" is used twice: 

Internal Exception: java.sql.SQLException: ORA-00918: column ambiguously defined

 

Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms