5

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;
Jacob
  • 14,463
  • 65
  • 207
  • 320
Flash_Back
  • 565
  • 3
  • 8
  • 31
  • 1
    Try using fully qualified column names (e.g. `c.ID`) in the `order by` clause as well. –  Jun 23 '14 at 11:42
  • in the `order by` give table name im,id,link,iw – ashok_p Jun 23 '14 at 11:43
  • 1
    In your order by clause there are a number of unqualified columns (im, id, link, iw). Do any of those column names exist on more than one of the joined tables? At a glance, id seems like a likely culprit. – Brett Okken Jun 23 '14 at 11:43
  • Show your Java piece of code,this code is not fit for framing the answer! – Am_I_Helpful Jun 23 '14 at 12:28

5 Answers5

2

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
Flash_Back
  • 565
  • 3
  • 8
  • 31
  • Well done. Please note that that never use reserved key words for columns as well as for database objects. – Jacob Jun 24 '14 at 04:48
  • You're right, I'm gonna change it so I won't have such issues again. Thanks so much for your help Polppan ! ;) – Flash_Back Jun 24 '14 at 09:00
1

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;
ashok_p
  • 741
  • 3
  • 8
  • 17
  • Hello, thanks for your answer. Unfortunately this doesn't seem to work either and produces the same ORA-00918 error. In fact, if I make the same query with SELECT * this works fine, I think it may be caused by c.NAME, d.NAME and t.NAME in the SELECT. – Flash_Back Jun 23 '14 at 11:52
  • have you tried to execute the query directly in sql*plus or in the database server – ashok_p Jun 23 '14 at 11:57
  • Yes, the query I wrote on the first post works and produces the expected result in SQL Developer. However some mapping issue seems to happen when run in Java. The error is raised by the "addEntity" function. – Flash_Back Jun 23 '14 at 12:00
1

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;
Jacob
  • 14,463
  • 65
  • 207
  • 320
  • Seems very relevant too. The query doesn't work as it is but I think that, like João Mendes said, there's an Hibernate issue behind this. Since I'm mapping Concentrator objetcs, I guess I just need to find the right syntax for the aliases you gave me in the SELECT. – Flash_Back Jun 23 '14 at 12:46
  • Could you post your Java code snippet where you define entities? – Jacob Jun 23 '14 at 12:47
  • @Flash_Back Are you using the same alias names in order by clause? – Jacob Jun 23 '14 at 12:51
  • I edited my first post, hoping that this function was what you were asking for. Yes I gave it a try with your exact request, so should be the same alias names. However I think the Hibernate error is due to the fact that I'm mapping some Concentrator object, but there's also a Department field (d.name) and a type field (t.name) in the SELECT. I guess I must specifiy that d.name and t.name are not part of the Concentrator object somehow – Flash_Back Jun 23 '14 at 12:55
  • @Flash_Back In your Department entity class, map name column with some other names like `dName` so that it will be differ from other name column. Besides does Hibernate generate SQL statements before the exception is thrown? – Jacob Jun 23 '14 at 13:04
  • Thanks, I'll try this ! However I'm sorry I don't understand your question about the statements I'm afraid. All I know is the error and the line (addEntity) that raised it. – Flash_Back Jun 23 '14 at 13:20
  • @Flash_Back What I meant to say is about hibernate generated sql statement by opening sql trace. – Jacob Jun 23 '14 at 14:26
1

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.

João Mendes
  • 1,719
  • 15
  • 32
  • Thank you, sounds really relevant ! But how should I do with aliases ? I have three Java classes: Concentrator.java, Department.java and Type.java with the same field's name: "name". In the SELECT, should I write something like this "SELECT distinct c.name, d.name as department.name, t.name as type.name" or is it not the right syntax ? – Flash_Back Jun 23 '14 at 12:43
  • I think yes, you can do that, but don't forget to use the second form of `addEntity`: `addEntity("department", Department.class)`. You can check out [this other question](http://stackoverflow.com/questions/7506732/returning-multiple-object-types-using-hibernate-using-an-inner-join) for more ideas. – João Mendes Jun 23 '14 at 12:48
1

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

Community
  • 1
  • 1
Chris
  • 20,138
  • 2
  • 29
  • 43