Oracle ROWNUM

[Kiril Menshikov]

Hi,

I am trying to convert Oracle SQL to Redshift with some optimizations. 
Everything works good except ROWNUMs. Parser accept it as a filed and convert 
it to the same statement. But I want to get limit statement. Does anybody had 
similar problem? Or can point me how to transform statement or change rel 
algebra?

Example:
Oracle: SELECT NAME FROM USERS WHERE ROWNUM <= 5;
Redshift: SELECT NAME FROM USERS LIMIT 5;

Code sample:
String sql = “select name form users where rownum <= 5”;
SqlParser.Config config = SqlParser.configBuilder()
                .setLex(Lex.ORACLE)
                .setConformance(SqlConformanceEnum.ORACLE_12)
                .build();
DataSource dataSource = JdbcSchema.dataSource("jdbc:oracle:thin:….",
                "oracle.jdbc.OracleDriver", “user", “pass”);
SchemaPlus rootSchema = Frameworks.createRootSchema(false);

JdbcSchema schema =  JdbcSchema.create(rootSchema, “o", dataSource, null, “my") 
;
SchemaPlus instrumentation = rootSchema.add(“my", schema);
final FrameworkConfig config = Frameworks.newConfigBuilder()
                .parserConfig(parserConfig)
                .defaultSchema(instrumentation)
                .traitDefs(null)
                .costFactory(null)
                .context(Contexts.EMPTY_CONTEXT)
                .ruleSets(RuleSets.ofList())
                .programs(programs)
                .typeSystem(RelDataTypeSystem.DEFAULT)
                .build();
Planner planner = Frameworks.getPlanner(config);
SqlNode sqlNode = planner.parse(sql);
SqlNode validatedNode = planner.validate(sqlNode);
SqlDialect sqlDialect = SqlDialect.DatabaseProduct.REDSHIFT.getDialect();
String convertedSql = parse.toSqlString(sqlDialect).getSql();  //  will do the 
same SELECT NAME as name FROM USERS WHERE ROWNUM <= 5;

Thanks,
-Kiril

signature.asc
Description: Message signed with OpenPGP