I am using the DB2 dialect.
While using the Criteria api and putting the setMaxResults.
It is throwing SQLGrammarException.

List reasonCodeTypeCriteria = session.createCriteria(ReasonCodeType.class)
.add(Expression.eq("reasonCodeTypePk.reasonCode","DRE"))
.setMaxResults(10)
.list();



The corresponding sql generated is wrong:
It is:
[b]select * from ( [/b]select rownumber() over() as rownumber_, this_.EXCPN_REASON_CD as EXCPN1_0_, this_.EXCPN_TYPE_CD as EXCPN2_0_, this_.EXCPN_REASON_CD as EXCPN1_8_0_, this_.EXCPN_TYPE_CD as EXCPN2_8_0_ from A1PRD.EXCPN_REASON_TYPE this_ where this_.EXCPN_REASON_CD=? ) as temp_ where rownumber_ <= ?

An unexpected token "(" was found following "". Expected tokens may include: ", FROM INTO ". SQLSTATE=42601

Any help would be appreciated.
Thanks

Re-check your dialect usage -- "FETCH FIRST x ROWS ONLY" is the DB2 limiting clause not "rownumber_ <= ?"

Curtis ...

Hi All,
This must be related to your dialect .Please look at the following link to get your right dialect class and replace it in your properties file or where ever you specified your hibernate.dialect properties.

http://www.hibernate.org/hib_docs/v3/ap ... mmary.html


Note - I was getting the SQL generated like limiting clause not "rownumber_ <= ?" before I switch my dialect from org.hibernate.dialect.DBDialect to org.hibernate.dialect.DB2390Dialect.
Thanks to curtis for pointing this out.
Regards,
Vinodh