"Micky Li (JIRA)" <jira@apache.org> writes:
> After apply the derby-3634-c.diff patch, it compiles and jar OK. The
> SQL query string as I mentioned at 28/Sep/09 09:02 PM works well.
> My target is to use ROW_NUMBER() with ORDER BY in one SQL query, but
> there shows a error as following: 'Syntax error: Encountered "ORDER"
> at line 1, column 113'
> the SQL query string is
>
> SELECT * FROM ( SELECT ROW_NUMBER() OVER() AS rownum, event_log.* FROM event_log WHERE
event_log.id<=2147483647 ORDER BY id DESC) AS TR WHERE rownum >= 2189 AND rownum <=
2288
The problem here is that you have an ORDER BY in a sub-select, which
is currently not allowed by Derby. ORDER BY is only allowed on the
outer SELECT. This patch does allow you to use ROW_NUMBER() in the
order by clause, but this is not what you want, it seems.
E.g. SELECT ... ORDER BY ROW_NUMBER() OVER ()
is allowed by the patch
I plan to work on relaxing this restriction as part of the 10.6 work.
You could probably achieve what you want by using the new OFFSET/FETCH
FIRST syntax introduced in 10.5, cf.
http://db.apache.org/derby/docs/10.5/ref/rrefsqljoffsetfetch.html
Dag
>
> It clearly that the syntax error is caused by the ORDER which is
> added. But I think the derby-3634-c.diff patch is to solve this
> problem, am I right?
>
> Is there something I missed?
|