-1

I would like to create a SQL like this:

select ROW_NUMBER() over(order by orderno) as id from process

then I try to use "SqlSelect" to create parse tree, but I don't konw how to create function "ROW_NUMBER() over(order by orderno)". Maybe I should use "RexWindow" to create window aggregation? Can you please give some examples to me, thanks.

f1sh
  • 11,489
  • 3
  • 25
  • 51
qqqq
  • 1

1 Answers1

0

new a class like this:

public class CustomFun extends ReflectiveSqlOperatorTable {
private static CustomFun instance;

public static CustomFun instance() {
    if (instance == null) {
        // Creates and initializes the standard operator table.
        // Uses two-phase construction, because we can't initialize the
        // table until the constructor of the sub-class has completed.
        instance = new CustomFun();
        instance.init();
    }
    return instance;
}

public static final SqlSpecialOperator ROW_NUMBER_OVER =
        new SqlSpecialOperator(
                "ROW_NUMBER() OVER",
                SqlKind.OTHER_FUNCTION,
                0,
                true,
                ReturnTypes.BIGINT,
                null,
                OperandTypes.STRING) {
            public void unparse(
                    SqlWriter writer,
                    SqlCall call,
                    int leftPrec,
                    int rightPrec) {
                writer.print("ROW_NUMBER() OVER(ORDER BY "+call.getOperandList().get(0).toSqlString(DefaultDIalect.DEFAULT).toString()+")");
            }
        };}

then use it:

SqlNode sqlNode = new SqlIdentifier("orderno", SqlParserPos.ZERO);
    selectList.add(IndentifierUtil.as(new SqlBasicCall(CustomFun.ROW_NUMBER_OVER, new SqlNode[]{sqlNode}, SqlParserPos.ZERO), "prod_line_id"));
qqqq
  • 1