I have a pageable query set up and would like to order by a column in the underlying table but it doesn't appear to be possible. I want to pull rows out 15 at a time but order the entire table of 1000+ rows by different columns depending on what the user selects.

I looked at the sql generated by hibernate and he is doing:

select * from (select columns from table where ...) where rownum < whatever

This works great but when I put an order by in the HQL I get an error.

Great description. What is the error? Generated SQL? Executed HQL? Please give a proper description of the problem.

Sorry. The error is from oracle and it says unmatched right parentheses but that is not really the problem. I read that you cannot order by a subselect so given that Hibernate creates the sql for a pageable query as shown above, there isn't really a solution using that approach. So the question really is does anyone know of a way to create a pageable query ordered by the entire contents of the underlying table on some arbitrary column?

How about you give us something to work with here!

What is the actual generated SQL? What is the full text of the exception?


TIA.

OK. back at work so I can connect to target Oracle database:

My code snippet:
--------------------
Query query = session.createQuery(queryBuffer.toString());

/*--------------------------------------------------------------------*
* Set query parameters. *
*--------------------------------------------------------------------*/
query.setProperties(seatInfo);
if (history != null) {
query.setProperties(history);
}

/*--------------------------------------------------------------------*
* Set paging variables if passed in *
*--------------------------------------------------------------------*/
if (pageSize > 0) {
query.setMaxResults(pageSize);
query.setFirstResult(pageSize * pageNumber);
}
List finds = query.list();

+++++++++++++++++++++++++++++++++++++++++++++++

HQL I pass in queryBuffer:
-------------------------------
select seatInfo from SeatInfo as seatInfo where seatInfo.seatStatus like :seatStatus and seatInfo.seatOrgCode like :seatOrgCode order by seatInfo.seatName

+++++++++++++++++++++++++++++++++++++++++++++++

Stack Trace including generated SQL:
-------------------------------------------

Hibernate: select * from ( select seatinfo0_.ID as ID, seatinfo0_.SEAT_ID as SEAT_ID, seatinfo0_.SEAT_NBR as SEAT_NBR, seatinfo0_.SEAT_STATUS as SEAT_STA4_, seatinfo0_.SEAT_ORGCODE as SEAT_ORG5_, seatinfo0_.SEAT_NAME as SEAT_NAME, seatinfo0_.SEAT_CLIN as SEAT_CLIN, seatinfo0_.SEAT_TYPE as SEAT_TYPE, seatinfo0_.SEAT_FEE as SEAT_FEE, seatinfo0_.SUPPORT_FEE as SUPPORT10_, seatinfo0_.SEAT_CHARGE_JON as SEAT_CH11_, seatinfo0_.SEAT_ERROR_JON as SEAT_ER12_, seatinfo0_.AUDIT_USER_ID as AUDIT_U13_, seatinfo0_.AUDIT_DATE as AUDIT_DATE from nmcibsa.SEAT_INFO seatinfo0_ where (seatinfo0_.SEAT_STATUS like ? )and(seatinfo0_.SEAT_ORGCODE like ? ) order by seatinfo0_.SEAT_NAME ) where rownum <= ?
Feb 2, 2004 12:10:19 PM net.sf.hibernate.util.JDBCExceptionReporter logExceptions
WARNING: SQL Error: 907, SQLState: 42000
Feb 2, 2004 12:10:19 PM net.sf.hibernate.util.JDBCExceptionReporter logExceptions
SEVERE: ORA-00907: missing right parenthesis

Feb 2, 2004 12:10:19 PM net.sf.hibernate.util.JDBCExceptionReporter logExceptions
WARNING: SQL Error: 907, SQLState: 42000
Feb 2, 2004 12:10:19 PM net.sf.hibernate.util.JDBCExceptionReporter logExceptions
SEVERE: ORA-00907: missing right parenthesis

Feb 2, 2004 12:10:19 PM net.sf.hibernate.JDBCException <init>
SEVERE: Could not execute query
java.sql.SQLException: ORA-00907: missing right parenthesis

at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:168)
at oracle.jdbc.ttc7.TTIoer.processError(TTIoer.java:208)
at oracle.jdbc.ttc7.Oall7.receive(Oall7.java:543)
at oracle.jdbc.ttc7.TTC7Protocol.doOall7(TTC7Protocol.java:1405)
at oracle.jdbc.ttc7.TTC7Protocol.parseExecuteDescribe(TTC7Protocol.java:643)
at oracle.jdbc.driver.OracleStatement.doExecuteQuery(OracleStatement.java:1674)
at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1870)
at oracle.jdbc.driver.OraclePreparedStatement.executeUpdate(OraclePreparedStatement.java:363)
at oracle.jdbc.driver.OraclePreparedStatement.executeQuery(OraclePreparedStatement.java:314)
at org.apache.commons.dbcp.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:179)
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 mil.spawar.bsa.dataaccess.hibernate.SeatInfoDAO.findBy(SeatInfoDAO.java:161)
at mil.spawar.bsa.nmciservicecenter.actions.SeatInfoSearchAction.search(SeatInfoSearchAction.java:179)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
at java.lang.reflect.Method.invoke(Method.java:324)
at org.apache.struts.actions.DispatchAction.dispatchMethod(DispatchAction.java:280)
at org.apache.struts.actions.DispatchAction.execute(DispatchAction.java:216)
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:763)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:856)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:284)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:204)
at mil.spawar.bsa.system.HibernateStrutsFilter.doFilter(HibernateStrutsFilter.java:56)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:233)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:204)
at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:256)
at org.apache.catalina.core.StandardValveContext.invokeNext(StandardValveContext.java:151)
at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:563)
at org.apache.catalina.core.StandardContextValve.invokeInternal(StandardContextValve.java:245)
at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:199)
at org.apache.catalina.core.StandardValveContext.invokeNext(StandardValveContext.java:151)
at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:594)
at org.apache.catalina.core.StandardValveContext.invokeNext(StandardValveContext.java:149)
at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:563)
at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:195)
at org.apache.catalina.core.StandardValveContext.invokeNext(StandardValveContext.java:151)
at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:164)
at org.apache.catalina.core.StandardValveContext.invokeNext(StandardValveContext.java:149)
at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:563)
at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:156)
at org.apache.catalina.core.StandardValveContext.invokeNext(StandardValveContext.java:151)
at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:563)
at org.apache.catalina.core.ContainerBase.invoke(ContainerBase.java:972)
at org.apache.coyote.tomcat5.CoyoteAdapter.service(CoyoteAdapter.java:209)
at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:781)
at org.apache.coyote.http11.Http11Protocol$Http11ConnectionHandler.processConnection(Http11Protocol.java:549)
at org.apache.tomcat.util.net.TcpWorkerThread.runIt(PoolTcpEndpoint.java:589)
at org.apache.tomcat.util.threads.ThreadPool$ControlRunnable.run(ThreadPool.java:666)
at java.lang.Thread.run(Thread.java:536)


+++++++++++++++++++++++++++++++++++++++++++++++

I think Oracle is barking at the order by in the subselect. I get the same error when trying to execute the generated SQL from my SQL query tool. Maybe there is a better approach to what I am trying to do?

This kind of query works just perfectly on my Oracle install. Also I notice that noone else seems to be having any problem with this functionality (and we do have thousands of Oracle users). So I don't know what yr problem is...

Further research indicates this does NOT work in early versions of Oracle 8.x.x but may work in later versions or Oracle 9.x.x -- I don't know as I am stuck with an 8.x.x version.

Gavin, are you using a later version of Oracle than 8.0.6.0.0 which is our production version? If so that would explain why it works for you and not for me.

I am on Oracle 9, but I'm guessing that I would have heard screams of protest a few months ago if this did not work for current versions of Oracle 8.

Oracle 8.0 is a really bad and quite different to 8.1.

Confirmed that order by in a subselect/subquery is allowed in Oracle 8.1.7.0.0 and Gavin confirmed it works in 9.x -- for what it's worth if it ever comes up again.


Thanks for your attention to this Gavin. Your help is truly appreciated.

No problem, its good that we are now aware of this. Note that if you really need to run on Oracle 8.0, you could easily customize the dialect.

Just to add that using Hibernate 2.1.4 (using either Oracle Dialect), this issue causes the standard "build eg" to fail if attempting to run against Oracle 8.0.

Not an issue if using a later Oracle version.