Hello:

I was hoping someone might point me in a direction. The following query in Oracle returns the 25th to 50th records:

select * from (select a.*, ROWNUM rnum from ( select * from program order by id) a where ROWNUM <= 50 ) where rnum >= 25;

I need to use createSQLQuery to run this query, so I tried the following code:

List<Program> retVal3 = getHibernateTemplate().getSessionFactory().getCurrentSession()
.createSQLQuery("select * from (select a.*, ROWNUM rnum from ( select * from rtipsuser.program order by id) a where ROWNUM <= 50 ) where rnum >= 25")
.addEntity(gov.nih.nci.rtips.dbi.domain.Program.class)
.list();

I turned the Hibernate show_sql on and got the following error:

Hibernate: select * from (select a.*, ROWNUM rnum from ( select * from rtipsuser
.program order by id) a where ROWNUM <= 50 ) where rnum >= 25
ERROR [JDBCExceptionReporter.java : 101] Invalid column name

When I run the same query in SQL, works fine, all columns are returned. Had this worked, I was then going to start adding addJoin() lines for each of the one-to-many tables linked by the program table, e.g.,

.addJoin("program.rtipsAppUsers");

We are using Hibernate 3.3.1, according to the Hibernate.jar manifest file.

thank you in advance for any advice.
--Harvey