Hi Lukas, Your workaround works fine, thanks for the suggestion.
jOOQ is a wonderful library by the way, it makes working with sql in java a pleasure. Thanks for your work! Best, Mike On Wednesday, January 30, 2013 7:11:34 PM UTC+1, Lukas Eder wrote: > > Thanks for reporting this. This is likely a bug in the SQL Server specific > implementation of the OFFSET clause. I have registered #2136 for this: > https://github.com/jOOQ/jOOQ/issues/2136 > > As a workaround, try explicitly selecting all relevant columns from > [mwp_widget] and [mwp_widget2cat], possibly renaming one (or both) [MUN_ID] > columns > > Cheers > Lukas > > 2013/1/29 Mike Fayer <[email protected] <javascript:>> > >> Hi all, >> >> I'm new to jOOQ so apologies if this is a newbie error... >> >> I'm coding against sql server 2008 with jooq and am using limit/offset >> for pagination. When the query is executed without a join, everything works >> just fine. With a join, if the offset == 0, the query works. However, if >> the offset > 0 and there is a join, I get an exception. The limit seems to >> have no effect on the situation. >> >> The error: >> SEVERE: Servlet.service() for servlet [default] in context with path >> [/server] threw exception >> org.jooq.exception.DataAccessException: SQL >> [ >> select * from (select limit_107798178.*, row_number() over (order by >> [MUN_NAME] asc) >> as rownum_107798178 >> >> from (select top 100 percent [dbo].[mwp_widget].[MUN_ID], >> [dbo].[mwp_widget].[MUN_NAME], [dbo].[mwp_widget].[MUN_NUMBER], >> [dbo].[mwp_widget2cat].[MUN_ID], [dbo].[mwp_widget2cat].[RCA_ID] >> >> from [dbo].[mwp_widget] join [dbo].[mwp_widget2cat] >> on [dbo].[mwp_widget].[MUN_ID] = [dbo].[mwp_widget2cat].[MUN_ID] >> >> where ([dbo].[mwp_widget].[MUN_NUMBER] is not null and >> [dbo].[mwp_widget].[MUN_NAMEID] like ? and ([dbo].[mwp_widget2cat].[RCA_ID] >> = ? or [dbo].[mwp_widget2cat].[RCA_ID] = ?)) order by >> [dbo].[mwp_widget].[MUN_NAME] asc) as limit_107798178) as >> outer_limit_107798178 where rownum_107798178 > ? and rownum_107798178 <= (? >> + ?) >> ]; >> >> The column 'MUN_ID' was specified multiple times for 'limit_107798178'. >> >> ---- >> >> Thanks in advance! >> >> Mike >> >> -- >> You received this message because you are subscribed to the Google Groups >> "jOOQ User Group" group. >> To unsubscribe from this group and stop receiving emails from it, send an >> email to [email protected] <javascript:>. >> For more options, visit https://groups.google.com/groups/opt_out. >> >> >> > > -- You received this message because you are subscribed to the Google Groups "jOOQ User Group" group. To unsubscribe from this group and stop receiving emails from it, send an email to [email protected]. For more options, visit https://groups.google.com/groups/opt_out.
