Thanks for the fast response Lukas and for sure thanks for the hint with the "hint" of Jooq :).
I'll take a look and tell how it worked out and how I solve it, but thanks in advance for the tip! Actually I want to figure out the total in a column of the select before limiting the result. In Oracle I would do it probably by selecting the data within an inner select and a field with the window function "COUNT(*) OVER()" which is also provided by Jooq and works for me on Oracle, SQL Server. Unfortunately MySql does not support this functionality and you have to do it with the nice but different "FOUND_ROWS()" function. H2 not support it yet, but might upcoming with the 1.4.X verison. As you probably know because I saw that you requested some window function for H2 in the H2 community :). Thanks a lot, Cheers Michael Am Sonntag, 11. November 2012 19:22:52 UTC+1 schrieb Lukas Eder: > > That's quite an interesting MySQL feature. Looks much like an Oracle > hint to me. In Oracle, you would write: > > SELECT /*+INDEX(...)*/ * FROM t WHERE ... > > So if this SQL_CALC_FOUND_ROWS option is to be put between the SELECT > (DISTINCT) keyword(s) and the SELECT column expression list, you could > use jOOQ's .hint() method for that: > > http://www.jooq.org/javadoc/latest/org/jooq/SelectFromStep.html#hint(java.lang.String) > > > An example is given in the manual: > > http://www.jooq.org/doc/2.6/manual/sql-building/sql-statements/select-statement/oracle-hints/ > > > Let me know if this works for you. If it does, I'll add some more > documentation to the hint() method, then > > Cheers > Lukas > > 2012/11/11 <[email protected] <javascript:>>: > > Is it possible to add/render the "SQL_CALC_FOUND_ROWS" which is > necessary > > for the by MySql FOUND_ROWS() function to the Jooq-Select Statement to > > retrieve the total of the result set not limit by the limit statement ? > > > > MySql Reference: FOUND_ROWS() > > > > My workaround would be to add "SQL_CALC_FOUND_ROWS" as a Jooq-Field but > jooq > > will render the comma ',' after the "SQL_CALC_FOUND_ROWS" which has to > be > > removed after rendering. Not that nice workaround which I'd like to > avoid. > > > > Thanks. >
