I had to make a couple minor adjustments to the original query to make it work
properly. It seems if you do not alias the DBFuncExpr() AND the subquery you
get weird results. Here is what works for me:
DBColumnExpr ROW_NUM = new DBFuncExpr(auditDb.T_AUDIT_RECORDS.C_ID,
"ROW_NUMBER() OVER (ORDER BY ?)", null, null, false, DataType.INTEGER);
DBCommand subCmd = auditDb.createCommand();
subCmd.select(auditDb.T_AUDIT_RECORDS.C_ID);
subCmd.select(auditDb.T_AUDIT_RECORDS.C_DESCRIPTION);
subCmd.select(auditDb.T_AUDIT_RECORDS.C_APPLICATION);
subCmd.select(auditDb.T_AUDIT_RECORDS.C_CATEGORY);
subCmd.select(auditDb.T_AUDIT_RECORDS.C_UUID);
subCmd.select(auditDb.T_AUDIT_RECORDS.C_DATE_CREATED);
subCmd.select(auditDb.T_AUDIT_RECORDS.C_PAYLOAD);
subCmd.select(ROW_NUM.as("R")); // <-- This seems to be
important
subCmd.where(auditDb.T_AUDIT_RECORDS.C_APPLICATION.is(applicationName));
DBQuery subQuery = new DBQuery(subCmd);
DBCommand mainCmd = auditDb.createCommand();
mainCmd.select(subQuery.findQueryColumn(auditDb.T_AUDIT_RECORDS.C_ID));
mainCmd.select(subQuery.findQueryColumn(auditDb.T_AUDIT_RECORDS.C_APPLICATION));
mainCmd.select(subQuery.findQueryColumn(auditDb.T_AUDIT_RECORDS.C_VERSION));
mainCmd.select(subQuery.findQueryColumn(auditDb.T_AUDIT_RECORDS.C_DESCRIPTION));
mainCmd.select(subQuery.findQueryColumn(auditDb.T_AUDIT_RECORDS.C_CATEGORY));
mainCmd.select(subQuery.findQueryColumn(auditDb.T_AUDIT_RECORDS.C_UUID));
mainCmd.select(subQuery.findQueryColumn(auditDb.T_AUDIT_RECORDS.C_DATE_CREATED));
mainCmd.select(subQuery.findQueryColumn(auditDb.T_AUDIT_RECORDS.C_PAYLOAD));
mainCmd.where(subQuery.findQueryColumn(ROW_NUM.as("R")).isBetween(first, first
+ count)); // <-- Again. Note that first and count is offset and limit
respectively.
Will see now if I can access the wiki. :)
I have not tried with a DESC order yet. Will see what happens, but I don't
think it should be a problem as the rowid's are applied after the sort.
---Jaco
-----Original Message-----
From: McKinley [mailto:[email protected]]
Sent: Friday, January 22, 2010 12:34 PM
To: [email protected]
Subject: Re: Rowlimits: OFFSET and LIMIT
Thanks for reporting back. Did you use DESC in your ORDER BY? If you
don't have wiki access you might consider posting the wiki markup in
the issue tracker. I registered for the wiki, but didn't now get
access by default.
I'll be testing ROW_NUMBER on SQL Server tomorrow and will let you
know what I find.
Thanks,
McKinley
On Fri, Jan 22, 2010 at 10:21 AM, Jaco van Tonder <[email protected]> wrote:
> Rainer,
>
> Thanks for this info. This works well for Oracle. I would like to document
> this on the wiki for future reference until a proper limit/offset
> implementation can be implemented.
>
> ---Jaco
>
#####################################################################################
Attention:
The information contained in this message and or attachments is intended
only for the person or entity to which it is addressed and may contain
confidential and/or privileged material. Any review, retransmission,
dissemination or other use of, or taking of any action in reliance upon,
this information by persons or entities other than the intended recipient
is prohibited. If you received this in error, please contact the sender and
delete the material from any system and destroy any copies.
Thank You.
#####################################################################################
#####################################################################################
This e-mail message has been scanned for Viruses and Content and cleared
by MailMarshal
#####################################################################################
