[hibernate-issues] [Hibernate-JIRA] Commented: (HHH-7001) setMaxResults/offset generates illegal SQL for DB2 mainframe

[Brad Cupit (JIRA)]

    [ http://opensource.atlassian.com/projects/hibernate/browse/HHH-7001?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=45300#comment-45300 ] 

Brad Cupit commented on HHH-7001:
---------------------------------

Good question:

I'm using the standard org.hibernate.dialect.DB2Dialect

I tried the other two DB2 dialects but they didn't support offset.

> setMaxResults/offset generates illegal SQL for DB2 mainframe
> ------------------------------------------------------------
>
>                 Key: HHH-7001
>                 URL: http://opensource.atlassian.com/projects/hibernate/browse/HHH-7001
>             Project: Hibernate ORM
>          Issue Type: Bug
>          Components: core
>    Affects Versions: 4.0.0.Final
>         Environment: DB2 v9 for z/OS (mainframe)
>            Reporter: Brad Cupit
>              Labels: db2, dialect
>
> the fix for HHH-2176 works for DB2 LUW (Linux/Unix/Windows) but does not work on DB2 for z/OS.
> Specifically {{order of}} is not supported inside over():
> {{select * from ( select inner2_.*, rownumber() over(order by order of inner2_) as rownumber_ from ( ...}}
> I worked around this issue by extending the DB2 dialect and changing the above SQL to:
> {{select * from ( select inner2_.*, rownumber() over(order by inner2_.SORT_COLUMN_ALIAS_HERE) as rownumber_ from ( ...}}
> or for cases when the subquery isn't ordered:
> {{select * from ( select inner2_.*, rownumber() over() as rownumber_ from ( ...}}
> Which works for DB2 LUW and DB2 for z/OS.
> I could provide a pull request, but to do this I had to parse the original SQL. *Is the Hibernate team interested in a pull request if the dialect has to parse SQL?*

--
This message is automatically generated by JIRA.
For more information on JIRA, see: http://www.atlassian.com/software/jira