Hi, i have this query :

"from Aplicacion as a order by a.nombre"

when i try this query, i recive the following error (in Ibm DB2 7.1 UDB):

[IBM][CLI Driver][DB2/NT] SQL0104N An unexpected token "order" was found following "ICACION aplicacion0_". Expected tokens may include: "GROUP". SQLSTATE=42601

Sql :

select * from ( select rownumber() over(order by aplicacion0_.APLNOMBRE) as row_, aplicacion0_.APL_ID as APL_ID, aplicacion0_.APLNOMBRE as APLNOMBRE, aplicacion0_.DESC_APL as DESC_APL from APLICACION aplicacion0_ order by aplicacion0_.APLNOMBRE ) as temp_ where row_ between ?+1 and ?

Thank

This is not the HQL that generate this SQL statement

i try this :

Query q = HibernateSession.currentSession().createQuery("from Aplicacion as a order by a.nombre");
q.setFirstResult(2);
q.setMaxResults(2);
q.list();

the sql :

Hibernate: select * from ( select rownumber() over(order by aplicacion0_.APLNOMBRE) as row_, aplicacion0_.APL_ID as APL_ID, aplicacion0_.APLNOMBRE as APLNOMBRE, aplicacion0_.DESC_APL as DESC_APL from APLICACION aplicacion0_ order by aplicacion0_.APLNOMBRE ) as temp_ where row_ between ?+1 and ?

and the mapping :

<hibernate-mapping>

<class name="xxx.Aplicacion" table="APLICACION" dynamic-insert="true" dynamic-update="true">

<id name="id" type="long" column="APL_ID" unsaved-value="-1">
<generator class="native"/>
</id>

<property name="nombre">
<column name="APLNOMBRE" sql-type="varchar(50)"/>
</property>

<property name="descripcion">
<column name="DESC_APL" sql-type="varchar(50)"/>
</property>

....
</class>
</hibernate-mapping>

Might be an incompatibility with DB2 7 - try to get the query working directly by editing the SQL, and if you can, post again so the dialect can be adjusted (have no DB2 to test here)

The query without the second "order by" functions ok (check http://troels.arvin.dk/db/rdbms/ ) :

select * from (
select rownumber() over(order by aplicacion0_.APLNOMBRE) as row_,
aplicacion0_.APL_ID as APL_ID, aplicacion0_.APLNOMBRE as APLNOMBRE,
aplicacion0_.DESC_APL as DESC_APL from APLICACION aplicacion0_ ) as temp_
where row_ between 1 and 2

this query does not function :

select * from (
select
aplicacion0_.APL_ID as APL_ID,
aplicacion0_.APLNOMBRE as APLNOMBRE,
aplicacion0_.DESC_APL as DESC_APL
from APLICACION aplicacion0_
order by aplicacion0_.APLNOMBRE )
as temp_

Error: SQL0104N An unexpected token "order" was found following "ICACION aplicacion0_".
Expected tokens may include: "GROUP". SQLSTATE=42601
(State:42601, Native Code: FFFFFF98)

may be is something (bug) with an inner query with "order by" .

bye

And does it do the order? If yes, submit a JIRA entry.

Add it to JIRA please.

a quick and dirty patch to db2dialect:

...
StringBuffer pagingSelect = new StringBuffer( sql.length()+100 )
.append("select * from ( ")
// Patch here
.append(orderByIndex>0 ? sql.substring(0,orderByIndex) : sql)
// end patch
.insert( getAfterSelectInsertPoint(sql), rownumber.toString() )
.append(" ) as temp_ where row_ ");
...

bye