I want to use TRIM function in my HQL. This is the query in SQL form:

select A.CODE, trim(A.DSCR) from CODETABLE A where (A.CONDITION='100') order by trim(A.DSCR)

I want to write this query in HQL. This is my code in Hibernate:

StringBuffer qryStr = new StringBuffer();
qryStr.append("Select A.CODE, trim(A.DSCR) ");
qryStr.append(" from CODETABLE A ");
qryStr.append(" WHERE A.CONDITION = '100' ");
qryStr.append(" ORDER BY trim(A.DSCR)");
Query qry = session.createQuery(qryStr.toString());
List lst = qry.list();

When the query is executed, Hibernate throws the exception listed below.

Please let me know what mistake I am committing here in the usage of the TRIM function. My query runs fine without the TRIM function.

I am using DB2 and Hibernate 3.0 and the dialect is DB2390Dialect.

org.hibernate.exception.SQLGrammarException: could not execute query at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:59) at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:43) at
org.hibernate.loader.Loader.doList(Loader.java:1502) at
org.hibernate.loader.Loader.list(Loader.java:1482) at
org.hibernate.loader.hql.QueryLoader.list(QueryLoader.java:365) at
org.hibernate.hql.ast.QueryTranslatorImpl.list(QueryTranslatorImpl.java:268) at
org.hibernate.impl.SessionImpl.list(SessionImpl.java:782) at
org.hibernate.impl.QueryImpl.list(QueryImpl.java:74) at
com.met.us.aoc.dao.AOCUtilityDAO.getPaymentTypes(AOCUtilityDAO.java) at
com.met.us.aoc.struts.action.AOCSearchTabAction.execute(AOCSearchTabAction.java:81) at
org.apache.struts.action.RequestProcessor.processActionPerform(RequestProcessor.java:431) at
org.apache.struts.action.RequestProcessor.process(RequestProcessor.java:236) at
org.apache.struts.action.ActionServlet.process(ActionServlet.java:1196) at
org.apache.struts.action.ActionServlet.doGet(ActionServlet.java:414) at
javax.servlet.http.HttpServlet.service(HttpServlet.java:743) at
javax.servlet.http.HttpServlet.service(HttpServlet.java:856) at
com.ibm.ws.webcontainer.servlet.ServletWrapper.service(ServletWrapper.java:966) at
com.ibm.ws.webcontainer.servlet.ServletWrapper.handleRequest(ServletWrapper.java:478) at
com.ibm.ws.wswebcontainer.servlet.ServletWrapper.handleRequest(ServletWrapper.java:463) at
com.ibm.ws.webcontainer.webapp.WebApp.handleRequest(WebApp.java:3129) at
com.ibm.ws.webcontainer.webapp.WebGroup.handleRequest(WebGroup.java:238) at
com.ibm.ws.webcontainer.WebContainer.handleRequest(WebContainer.java:811) at
com.ibm.ws.wswebcontainer.WebContainer.handleRequest(WebContainer.java:1433) at
com.ibm.ws.webcontainer.channel.WCChannelLink.ready(WCChannelLink.java:93) at
com.ibm.ws.http.channel.inbound.impl.HttpInboundLink.handleDiscrimination(HttpInboundLink.ja
va:465) at
com.ibm.ws.http.channel.inbound.impl.HttpInboundLink.handleNewInformation(HttpInboundLink.java:394) at
com.ibm.ws.http.channel.inbound.impl.HttpICLReadCallback.complete(HttpICLReadCallback.java:02) at
com.ibm.ws.tcp.channel.impl.AioReadCompletionListener.futureCompleted(AioReadCompletionListener.java:152) at
com.ibm.io.async.AbstractAsyncFuture.invokeCallback(AbstractAsyncFuture.java:213) at
com.ibm.io.async.AbstractAsyncFuture.fireCompletionActions(AbstractAsyncFuture.java:195) at
com.ibm.io.async.AsyncFuture.completed(AsyncFuture.java:136) at
com.ibm.io.async.ResultHandler.complete(ResultHandler.java:194) at
com.ibm.io.async.ResultHandler.runEventProcessingLoop(ResultHandler.java:782) at
com.ibm.io.async.ResultHandler$2.run(ResultHandler.java:863) at
com.ibm.ws.util.ThreadPool$Worker.run(ThreadPool.java:1510) Caused by:
COM.ibm.db2.jdbc.DB2Exception: [IBM][CLI Driver][DB2] SQL0214N An expression in the ORDER BY
clause in the following position, or starting with "1" in the "ORDER BY" clause is not valid. Reason code = "2". SQLSTATE=42822 at
COM.ibm.db2.jdbc.app.SQLExceptionGenerator.throw_SQLException(Unknown Source) at
COM.ibm.db2.jdbc.app.SQLExceptionGenerator.throw_SQLException(Unknown Source) at
COM.ibm.db2.jdbc.app.SQLExceptionGenerator.check_return_code(Unknown Source) at
COM.ibm.db2.jdbc.app.DB2PreparedStatement.execute2(Unknown Source) at
COM.ibm.db2.jdbc.app.DB2PreparedStatement.executeQuery(Unknown Source) at
com.ibm.ws.rsadapter.jdbc.WSJdbcPreparedStatement.pmiExecuteQuery(WSJdbcPreparedStatement.java:878) at
com.ibm.ws.rsadapter.jdbc.WSJdbcPreparedStatement.executeQuery(WSJdbcPreparedStatement.java:559) at org.hibernate.jdbc.AbstractBatcher.getResultSet(AbstractBatcher.java:107) at
org.hibernate.loader.Loader.getResultSet(Loader.java:1183) at
org.hibernate.loader.Loader.doQuery(Loader.java:363) at
org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:203) at
org.hibernate.loader.Loader.doList(Loader.java:1499) ... 32 more