ORA-00918：“列定义不明确”
ORA-00918：“列定义不明确”
提问于 2014-06-23 11:36:58
几天来，我一直试图理解甲骨文为什么要提出这个错误，但却找不到任何帮助我阅读所有相关主题的解决方案。我希望有人能帮我。我正在处理这个查询：
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;这在上非常好，但在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)由我的程序上的这一行(其中querySelect是相关的SQLQuery对象)引起的：
List<T> list = (List<T>) querySelect.addEntity(referenceClass).list();我需要将c.NAME、d.NAME和t.NAME保存在SELECT中，因为我不知道如何做到这一点而不引发此错误.我在SELECT中尝试了别名，但也没有起作用。
编辑：
似乎是一个Hibernate问题，而不仅仅是一个SQL问题。下面是在"addEntity“行上引发错误的函数。在这种情况下，预期它将返回Con集中器的对象的列表。
@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;
}下面是集中器的对象字段：
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;Stack Overflow用户
发布于 2014-06-23 14:33:30
多亏你们大家，我终于找到了解决办法！我接受了若昂·门德斯的答案，因为我实际上用正确的别名解决了问题，但所有其他答案也是相关的，并帮助我解决了问题。
这是最后一个查询。在DISTINCT中，我放置了所有映射对象的(集中器)字段，加上使用正确别名在ORDER BY中使用的两个字段。我想不是最漂亮的，但效果很好！
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 页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/24364924
复制相关文章
相似问题
腾讯云开发者
