Wow, I had to really hack to make DBFuncExpr work with multiple
order-by columns. Take note of the SQL comment in my function
template. This is because I could not cleanly use the question mark
with a DBOrderByInfo (yes, I know, I know). Anyway, the Object[]
params work great on the {#}, but the ? want the first argument to be
a DBColumnExpr and not a DBExpr which DBOrderByInfo. I tried to have
the first column hit the ? and direct the " DESC" or "" string to the
first {0}. That got type checked to DATETIME (the type of the column)
in this case. If I just ignore using ? every works great.
Any thoughts on how this usage might be facilitated by changes to the
function and abstract function arrangement?
public void page(int limit, int offset, DBOrderByInfo[] orderBy) {
this.limit = limit;
this.offset = offset;
this.orderBy = orderBy;
if(rowNum != null) subCmd.removeSelect(rowNum);
StringBuilder sql = new StringBuilder("ROW_NUMBER() OVER (ORDER
BY {0}");
if(rowNumParams == null || rowNumParams.length !=
orderBy.length)
rowNumParams = new Object[orderBy.length];
rowNumParams[0] = orderBy[0];
for(int i = 1; i < orderBy.length; i++){
rowNumParams[i] = orderBy[i];
sql.append(", {").append(i).append('}');
}
sql.append(')').append("--remove ? \n"); // HACK!!!
rowNum = new DBFuncExpr(orderBy[0].expr, sql.toString(),
rowNumParams, null, false, DataType.INTEGER)
.as("RowNum");
subCmd.select(rowNum);
subQuery = new DBQuery(subCmd);
this.select(subQuery.getQueryColumns());
this.select.remove(subQuery.findQueryColumn(etsr.UserID));
this.where(subQuery.findQueryColumn(rowNum).isBetween(offset,
offset + limit));
}
Thanks,
McKinley
>>>> 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.
