ORA-00918: "Column ambiguously defined"

Question

I'va been trying to understand why Oracle is raising this error for days, but couldn't find any solution that helped me in all the related topics I read. I'm hoping that someone would help me. I'm working on this query :

SELECT distinct c.NAME, c.SUPERVISIONNAME, c.INTERNALADDRESS, c.IM, c.ID, c.LINK, c.IW, d.NAME, t.NAME
FROM "CONCENTRATOR" c
LEFT OUTER JOIN "CONCENTRATOR_GROUP" USING(CONCENTRATOR_ID)
LEFT OUTER JOIN "GROUP" g USING(GROUP_ID)
LEFT OUTER JOIN "TYPE" t USING(TYPE_ID)
LEFT OUTER JOIN "DEPARTMENT" d USING(DEPARTMENT_ID)
WHERE TRIM(UPPER(t.NAME)) = 'type'
ORDER BY im DESC, id DESC, link DESC, iw DESC, TRIM(UPPER(d.name)) ASC, TRIM(UPPER(c.name)) ASC;

This works perfectly fine on SQL Developer, but raises this error when run in Java:

java.sql.SQLSyntaxErrorException: ORA-00918: column ambiguously defined

at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:445)
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:396)
at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:879)
at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:450)
at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:192)
at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:531)
at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:207)
at oracle.jdbc.driver.T4CPreparedStatement.executeForDescribe(T4CPreparedStatement.java:884)
at oracle.jdbc.driver.OracleStatement.executeMaybeDescribe(OracleStatement.java:1167)
at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1289)
at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3584)
at oracle.jdbc.driver.OraclePreparedStatement.executeQuery(OraclePreparedStatement.java:3628)
at oracle.jdbc.driver.OraclePreparedStatementWrapper.executeQuery(OraclePreparedStatementWrapper.java:1493)
at org.apache.commons.dbcp.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:96)
at org.apache.commons.dbcp.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:96)
at sun.reflect.GeneratedMethodAccessor29.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
at java.lang.reflect.Method.invoke(Unknown Source)
at org.hibernate.engine.jdbc.internal.proxy.AbstractStatementProxyHandler.continueInvocation(AbstractStatementProxyHandler.java:122)
at org.hibernate.engine.jdbc.internal.proxy.AbstractProxyHandler.invoke(AbstractProxyHandler.java:81)
at com.sun.proxy.$Proxy39.executeQuery(Unknown Source)
at org.hibernate.loader.Loader.getResultSet(Loader.java:1978)
at org.hibernate.loader.Loader.doQuery(Loader.java:829)
at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:289)
at org.hibernate.loader.Loader.doList(Loader.java:2463)
at org.hibernate.loader.Loader.doList(Loader.java:2449)
at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2279)
at org.hibernate.loader.Loader.list(Loader.java:2274)
at org.hibernate.loader.custom.CustomLoader.list(CustomLoader.java:331)
at org.hibernate.internal.SessionImpl.listCustomQuery(SessionImpl.java:1585)
at org.hibernate.internal.AbstractSessionImpl.list(AbstractSessionImpl.java:224)
at org.hibernate.internal.SQLQueryImpl.list(SQLQueryImpl.java:156)
at com.francetelecom.visionet.server.persistance.dao.impl.TemplateDAOImpl.paginate(TemplateDAOImpl.java:282)
at com.francetelecom.visionet.server.persistance.dao.impl.ConcentratorDAOImpl.findByCriteriaTest(ConcentratorDAOImpl.java:545)

Caused by this line on my program (where querySelect is the related SQLQuery object):

List<T> list = (List<T>) querySelect.addEntity(referenceClass).list();

I need to keep c.NAME, d.NAME and t.NAME in the SELECT due to the ORDER BY and I don't know how to do this without raising this error... I tried aliased in the SELECT but didn't work either.

EDIT:

Seems to be an Hibernate issue , more than an SQL one. Here is the function raising the error, on the "addEntity" line. In that case, it is expected to return a list of Concentrator's object.

@Override
@SuppressWarnings("unchecked")
public PaginatedList<T> paginate(SQLQuery querySelect, SQLQuery queryCount, int page, int numPerPage) throws PersistanceException
{
    PaginatedList<T> pList = new PaginatedList<T>();
    try {
        int offset = 0;
        if (numPerPage > -1) {
            offset = page * numPerPage;
        }
        int totalAllPages = ((BigDecimal) queryCount.uniqueResult()).intValue();
        querySelect.setMaxResults(numPerPage);
        querySelect.setFirstResult(offset);
        List<T> listAll = (List<T>) querySelect.addEntity(referenceClass).list();
        pList.setItems(listAll);
        pList.setPage(page);
        pList.setPageSize(numPerPage);
        pList.setTotal(totalAllPages);
    } catch (HibernateException e) {
        throw new PersistanceException(e);
    }
    return pList;
}

Here are the Concentrator's object fields :

private String name;
private String supervisionName;
private String internalAddress;
private boolean activeAlarms;
private int im;
private int id;
private int iw;
private int link;
private Date lastUpdate;
private Type type;
private Department department;

Answer 1

I finally found the solution thanks to you all ! I accepted João Mendes' answer because I actually solved the problem with correct alias, but all other answers were also relevant and helped me out.

Here is the final query. In the DISTINCT I put all the mapped object's (Concentrator) fields, plus the two used in the ORDER BY using correct alias. Not the prettiest I guess, but worked fine !

SELECT DISTINCT CONCENTRATOR_ID, c.NAME, SUPERVISIONNAME, INTERNALADDRESS, ACTIVEALARMS, IM, ID, LINK, IW, LASTUPDATE, TYPE_ID, DEPARTMENT_ID, d.NAME as "department.name", t.NAME as "type.name"
FROM "CONCENTRATOR" c LEFT OUTER JOIN "CONCENTRATOR_GROUP" USING(CONCENTRATOR_ID)
LEFT OUTER JOIN "GROUP" g USING(GROUP_ID)
LEFT OUTER JOIN "TYPE" t USING(TYPE_ID)
LEFT OUTER JOIN "DEPARTMENT" d USING(DEPARTMENT_ID)
WHERE (g.ident = 1) OR (g.ident = 16) OR (g.ident = 44)
AND (c.iw) > 0
AND TRIM(UPPER(t.name)) = 'OTELO'
ORDER BY im DESC, id DESC, link DESC, iw DESC, TRIM(UPPER(d.name)) ASC, TRIM(UPPER(c.name)) ASC 

Answer 2

This looks like a Hibernate thing, rather than strictly a SQL problem.

I think addEntity requires all columns to have distinct names, that match the fields in the entity being added. Oracle, alas, has no problem returning results with duplicate problem names, which is why your query works in SQL Developer.

Try giving all your columns in the SELECT clause distinct aliases, specifically, aliases that match the field members in your entity.

Answer 3

I think this may work for you..

Use fully qualified names to all columns in the order by clause.

SELECT distinct c.NAME, c.SUPERVISIONNAME, c.INTERNALADDRESS, c.IM, c.ID, c.LINK, c.IW, d.NAME, t.NAME
FROM "CONCENTRATOR" c
LEFT OUTER JOIN "CONCENTRATOR_GROUP" USING(CONCENTRATOR_ID)
LEFT OUTER JOIN "GROUP" g USING(GROUP_ID)
LEFT OUTER JOIN "TYPE" t USING(TYPE_ID)
LEFT OUTER JOIN "DEPARTMENT" d USING(DEPARTMENT_ID)
AND TRIM(UPPER(t.NAME)) = 'type'
ORDER BY c.IM DESC, c.ID DESC, c.LINK DESC, c.IW DESC, TRIM(UPPER(d.name)) ASC, TRIM(UPPER(c.name)) ASC;

Answer 4

Try using the following query, you have three name columns and I have renamed it has fname, sname and tname. Rename those the they you would like to, with unique names though.

Also note that you have used some of reserved keywords, so try having alias names for those as well, I have renamed it though. It is always better to avoid using reserved key words while creating database objects in order to avoid errors.

SELECT DISTINCT c.NAME FNAME,
                  c.SUPERVISIONNAME SUPERVISIONNAME,
                  c.INTERNALADDRESS INTERNALADDRESS,
                  c.IM IM,
                  c.ID T_ID,
                  c.LINK T_LINK,
                  c.IW T_IW,
                  d.NAME SNAME,
                  t.NAME TNAME
    FROM CONCENTRATOR c
         LEFT OUTER JOIN CONCENTRATOR_GROUP
            USING (CONCENTRATOR_ID)
         LEFT OUTER JOIN GROUP g
            USING (GROUP_ID)
         LEFT OUTER JOIN TYPE t
            USING (TYPE_ID)
         LEFT OUTER JOIN DEPARTMENT d
            USING (DEPARTMENT_ID)
   WHERE TRIM (UPPER (t.NAME)) = 'type'
ORDER BY im DESC,
         T_ID DESC,
         T_LINK DESC,
         T_IW DESC,
         TRIM (UPPER (SNAME)) ASC,
         TRIM (UPPER (FNAME)) ASC;

Answer 5

Hibernate is likely wrapping the SQL within another query to use Oracle's rownum in filtering the maxresults and firstrows. This will mean that any duplicates (c.Name and d.NAME) will cause exceptions unless aliased as described here: Column ambiguously defined in subquery using rownums .

Other JPA providers (EclipseLink for instance) will automatically alias fields when using pagination, so you might want to check if a later Hibernate version does the same or pull the patch described here https://hibernate.atlassian.net/browse/HHH-951 Another duplicate bug that describes the issue is https://hibernate.atlassian.net/browse/HHH-1436