Hi Lukas, I didn't think about looking in the open issues, sorry...
I've played around a bit with your examples and somehow I don't get how the ORDER BY is actually handled. If I specify an index, the sort order of the result seems not to be effected at all. I can even specify a negative index or one which should be too large and the query still executes fine. Postgres: http://sqlfiddle.com/#!12/d41d8/556 Oracle: http://sqlfiddle.com/#!4/d41d8/8513 In the execution plan anything else than a column name in the ORDER BY clause is translated to ORDER BY NULL. Maybe the value is not treated as index but as a constant? Even ORDER BY 'a' seems valid and is translated to NULL. But then, what would be the sense of an ORDER BY CONSTANT?... Cheers, Jörg Von: [email protected] [mailto:[email protected]] Im Auftrag von Lukas Eder Gesendet: Dienstag, 5. März 2013 19:38 An: [email protected] Betreff: Re: Window functions in Oracle Hi Jörg, Yes, this is a well-known issue: https://github.com/jOOQ/jOOQ/issues/1535 In the mean time, you can work around this issue by explicitly specifying an ORDER BY clause to your ranking function. Unfortunately, I hadn't found a reliable, database-agnostic way to simulate OVER() in oracle. Contrary to your suggestion, OVER() and OVER(ORDER BY 1) are not the same. - OVER(): Assign ranking values ordered by the query's ORDER BY clause (or implicitly by "ROWNUM" in Oracle) - OVER(ORDER BY 1): Assign ranking values ordered by the first column from the projection Examples: -- Postgres with d(a, b) as ( select 1, 2 union all select 3, 4 union all select 5, 6 ) select d.a, d.b, row_number() over() from d order by a desc -- Results: -- http://sqlfiddle.com/#!12/d41d8/549 -- Oracle 1 with d(a, b) as ( select 1, 2 from dual union all select 3, 4 from dual union all select 5, 6 from dual ) select d.a, d.b, row_number() over(order by rownum) from d order by a desc -- Results: -- http://sqlfiddle.com/#!4/d41d8/8475 -- Oracle 2 with d(a, b) as ( select 1, 2 from dual union all select 3, 4 from dual union all select 5, 6 from dual ) select d.a, d.b, row_number() over(order by 1) from d order by a desc -- Results: -- http://sqlfiddle.com/#!4/d41d8/8476 I'm not 100% sure yet, whether ORDER BY ROWNUM is correct. Optionally, jOOQ could introspect the "surrounding" query's ORDER BY clause. Any further input is welcome! Cheers Lukas 2013/3/5 Schönfisch, Jörg <[email protected]<mailto:[email protected]>> Hi, I have encountered a problem using OVER() in an Oracle database. I am adding two fields to a SelectQuery like this: Select.add(Factory.count().over(), Factory.rowNumber().over()); This translates to SELECT count(*) OVER (), FROM ..., which gives me an error about a missing ORDER BY in the OVER clause. In Postgres, this works without problems. IMHO, jOOQ should expand the OVER() for Oracle to OVER(ORDER BY 1). Is this possible? Cheers, Jörg -- 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]<mailto:jooq-user%[email protected]>. 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]<mailto:[email protected]>. 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.
