I Hate DB2/400 (iSeries) or at least V5R1
SEVERE: SQLException occurred<br>java.sql.SQLException: [SQL0208] ORDER BY column WRID or expression not in result table.
Discovered that was because Hibernate “auto generated” alias for any column in its select clause is not used in the ORDER BY clause (real column name is used instead).
A DB2/400 deficiency as many other databases understands the original column name in the Order By part.
As a workaround I used an integer in the Order By clause (ORDER BY 1) and it worked with HQL (from <table> order by 1 asc) but not in the criteria API (Order.asc(”1″));
The problem is seemingly resolved in DB2/400 V5R2 and I’m on V5R1.
I’ll add this to my list of DB2/400 hate reasons (No DB autogenerated IDs, no recursive queries, no resultset offest)… and the list goes on.
Hibernate Forum (DB2/400 Order By) …
It also seems that not many DB2 users are using Hibernate (scarce postings and forums). I hope I made the right decision by choosing Hibernate to go with this DB.