Dear All,

Iam using hibernate2.0. My problem is like, when Iam trying to use 'Distinct and Orderby' key words in my query using Hibernate2.0 giving exception.

my query as follows:

select distinct new QuirkVO(qvo.quirkID,qvo.title,qvo.description) from com.phoenixcolor.pcs.vo.quirk.QuirkVO as qvo , com.phoenixcolor.pcs.vo.quirk.AccountQuirkVO as aqvo, com.phoenixcolor.pcs.vo.quirk.QuirkAccountListVO qalvo where qvo.quirkID = aqvo.quirkID and aqvo.accountID = qalvo.accountID and lower(qalvo.account) like '%a%' order by lower(qvo.title)

The Exception as follows:

12:34:34,843 WARN [JDBCExceptionReporter] SQL Error: 0, SQLState: null
12:34:34,843 ERROR [JDBCExceptionReporter] ERROR: For SELECT DISTINCT, ORDER BY expressions must appear in target list

12:34:34,843 WARN [JDBCExceptionReporter] SQL Error: 0, SQLState: null
12:34:34,843 ERROR [JDBCExceptionReporter] ERROR: For SELECT DISTINCT, ORDER BY expressions must appear in target list

12:34:34,843 ERROR [JDBCExceptionReporter] Could not execute query
java.sql.SQLException: ERROR: For SELECT DISTINCT, ORDER BY expressions must appear in target list

at org.postgresql.core.QueryExecutor.execute(QueryExecutor.java:131)
at org.postgresql.jdbc1.AbstractJdbc1Connection.ExecSQL(AbstractJdbc1Connection.java:505)
at org.postgresql.jdbc1.AbstractJdbc1Statement.execute(AbstractJdbc1Statement.java:320)
at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:48)
at org.postgresql.jdbc1.AbstractJdbc1Statement.executeQuery(AbstractJdbc1Statement.java:153)
at net.sf.hibernate.impl.BatcherImpl.getResultSet(BatcherImpl.java:83)
at net.sf.hibernate.loader.Loader.getResultSet(Loader.java:794)
at net.sf.hibernate.loader.Loader.doQuery(Loader.java:188)
at net.sf.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:132)
at net.sf.hibernate.loader.Loader.doList(Loader.java:949)
at net.sf.hibernate.loader.Loader.list(Loader.java:940)
at net.sf.hibernate.hql.QueryTranslator.list(QueryTranslator.java:833)
at net.sf.hibernate.impl.SessionImpl.find(SessionImpl.java:1475)
at net.sf.hibernate.impl.QueryImpl.list(QueryImpl.java:39)
at com.phoenixcolor.pcs.dao.quirk.QuirkDAOImpl.searchQuirk(QuirkDAOImpl.java:651)
at com.phoenixcolor.pcs.web.struts.quirk.SearchQuirkAction.run(SearchQuirkAction.java:86)
at com.phoenixcolor.pcs.web.struts.common.BaseAction.execute(BaseAction.java:63)
at org.apache.struts.action.RequestProcessor.processActionPerform(RequestProcessor.java:484)
at org.apache.struts.action.RequestProcessor.process(RequestProcessor.java:274)
at org.apache.struts.action.ActionServlet.process(ActionServlet.java:1482)
at org.apache.struts.action.ActionServlet.doPost(ActionServlet.java:525)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:760)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:853)
at org.mortbay.jetty.servlet.ServletHolder.handle(ServletHolder.java:354)
at org.mortbay.jetty.servlet.WebApplicationHandler.dispatch(WebApplicationHandler.java:294)
at org.mortbay.jetty.servlet.ServletHandler.handle(ServletHandler.java:567)
at org.mortbay.http.HttpContext.handle(HttpContext.java:1807)
at org.mortbay.jetty.servlet.WebApplicationContext.handle(WebApplicationContext.java:525)
at org.mortbay.http.HttpContext.handle(HttpContext.java:1757)
at org.mortbay.http.HttpServer.service(HttpServer.java:879)
at org.jboss.jetty.Jetty.service(Jetty.java:456)
at org.mortbay.http.HttpConnection.service(HttpConnection.java:790)
at org.mortbay.http.HttpConnection.handleNext(HttpConnection.java:961)
at org.mortbay.http.HttpConnection.handle(HttpConnection.java:807)
at org.mortbay.http.SocketListener.handleConnection(SocketListener.java:197)
at org.mortbay.util.ThreadedServer.handle(ThreadedServer.java:276)
at org.mortbay.util.ThreadPool$PoolThread.run(ThreadPool.java:511)

Can any one help me in this regard?

Thanks in Advance.

Add lower(qvo.title) into the select clause, it is required by your DB

I tried by adding lower(qvo.title) into the select clause, it is giving exception as undefined alias.Any thing more Do I need to do?

Thanks in advance.

Your DB told you this is an undefined alias?

I have a simular query and the same error!
SELECT DISTINCT, ORDER BY expressions must appear in select list

If I take the distinct out the query works just fine but has duplicates. How do I use dynamic instances and remove dups ?

I take it back. In my case it seems that that
coalesce( transaction.amount, 0.0 ) is diff. than
coalesce( transaction.amount, 0 )

Your query should be

select distinct new QuirkVO(qvo.quirkID,lower(qvo.title) ,qvo.description) from com.phoenixcolor.pcs.vo.quirk.QuirkVO as qvo , com.phoenixcolor.pcs.vo.quirk.AccountQuirkVO as aqvo, com.phoenixcolor.pcs.vo.quirk.QuirkAccountListVO qalvo where qvo.quirkID = aqvo.quirkID and aqvo.accountID = qalvo.accountID and lower(qalvo.account) like '%a%' order by lower(qvo.title)

how is this going to be implemented for hibernate 3.0 ? Will the same query work ?

how is this going to be implemented for hibernate 3.0 ? Will the same query work ?