The ? may only be used once per template and it is replaced a source column
that this expression is based on.
Most functions are based on a single source expression which is provided as the
first param of DBFuncExpr like e.g. most string functions and aggregations.
The params parameter is for additional parameters that the sql function needs.
For each parameter a placeholder with a number wrapped in curly brackets is
required in the template.
Take e.g. the substring function. It requires a source column and two
additional params: the offset and the length.
Hence the template is "substring(?, {0}, {1})".
Most (but not all) column transformations can be declared using a DBFuncExpr.
Regards
Rainer
McKinley wrote:
> Re: Rowlimits: OFFSET and LIMIT
>
> Thanks Rainer! Do you happen to know what the behavior on ? in the
> template when params is not null null? Does it ignore the first
> argument expr? I ask this because a more rigorous usage would probably
> look like this:
>
> Object[] params = {tableA.City, tableA.State, new
> DBOrderByInfo(tableA.Age, true), tableA.LastName, tableA.FirstName};
> DBFuncExpr(..., ROW_NUMBER() OVER (PARTITION BY ?, ? ORDER BY ?, ?, ?
> ), params, ...)
>
> Looking at the addSql methods that get called the Object[] params do
> get type checked for by DBExpr so I suppose the proper 'DESC' strings
> will get appended. This may be all that is needed then for most of us
> to proceed with some of our trickiest requirements. Formal support
> could be added in the future, but this function template looks like it
> will work.
>
> Thanks,
>
> McKinley
>
> On Tue, Jan 19, 2010 at 8:26 PM, Rainer Döbele <[email protected]>
> wrote:
> > Hi,
> >
> > If you have not found out already, the simplest way to implement the
> row number expression is:
> >
> > DBColumnExpr ROW_NUM = new DBFuncExpr(EMP.PHONE_NUMBER, "ROW_NUMBER()
> OVER (ORDER BY ?)", null, null, false, DataType.INTEGER);
> > cmd.select(ROW_NUM);
> > cmd.where(ROW_NUM.isBetween(51, 100));
> >
