Examples with SQLSelectStatement com.alibaba.druid.sql.ast.statement.SQLSelectStatement used on opensource projects

Example 56 with SQLSelectStatement

use of com.alibaba.druid.sql.ast.statement.SQLSelectStatement in project druid by alibaba.

the class OracleFlashbackQueryTest3 method test_isEmpty.

public void test_isEmpty() throws Exception {
    String sql = "SELECT DECODE(GROUPING(department_name), 1, 'All Departments', department_name) AS department," + "DECODE(GROUPING(job_id), 1, 'All Jobs', job_id) AS job, COUNT(*) AS \"Total Empl\", AVG(salary) * 12 AS \"Average Sal\" " + "FROM employees e, departments d\n" + "WHERE d.department_id = e.department_id\n" + "GROUP BY ROLLUP (department_name, job_id);\n";
    String expect = "SELECT DECODE(GROUPING(department_name), 1, 'All Departments', department_name) AS department, " + "DECODE(GROUPING(job_id), 1, 'All Jobs', job_id) AS job, COUNT(*) AS \"Total Empl\", AVG(salary) * 12 AS \"Average Sal\"\n" + "FROM employees e, departments d\n" + "WHERE d.department_id = e.department_id\n" + "GROUP BY ROLLUP(department_name, job_id);\n";
    OracleStatementParser parser = new OracleStatementParser(sql);
    SQLSelectStatement stmt = (SQLSelectStatement) parser.parseStatementList().get(0);
    String text = TestUtils.outputOracle(stmt);
    Assert.assertEquals(expect, text);
    System.out.println(text);
}

Example 57 with SQLSelectStatement

use of com.alibaba.druid.sql.ast.statement.SQLSelectStatement in project druid by alibaba.

the class OracleGroupingSetTest method test_interval.

public void test_interval() throws Exception {
    String sql = "SELECT channel_desc, calendar_month_desc, co.country_id, " + "TO_CHAR(sum(amount_sold) , '9,999,999,999') AS SALES$\n" + "FROM sales, customers, times, channels, countries co\n" + "WHERE sales.time_id = times.time_id AND sales.cust_id = customers.cust_id AND sales.channel_id = channels.channel_id " + "AND customers.country_id = co.country_id AND channels.channel_desc IN ('Direct Sales', 'Internet') " + "AND times.calendar_month_desc IN ('2000-09', '2000-10') " + "AND co.country_id IN ('UK', 'US')\n" + "GROUP BY GROUPING SETS((channel_desc, calendar_month_desc, co.country_id), (channel_desc, co.country_id), " + "( calendar_month_desc, co.country_id) );\n";
    String expected = //
    "SELECT channel_desc, calendar_month_desc, co.country_id, " + //
    "TO_CHAR(SUM(amount_sold), '9,999,999,999') AS SALES$\n" + //
    "FROM sales, customers, times, channels, countries co\n" + //
    "WHERE sales.time_id = times.time_id" + //
    "\n\tAND sales.cust_id = customers.cust_id" + //
    "\n\tAND sales.channel_id = channels.channel_id" + //
    "\n\tAND customers.country_id = co.country_id" + //
    "\n\tAND channels.channel_desc IN ('Direct Sales', 'Internet')" + "\n\tAND times.calendar_month_desc IN ('2000-09', '2000-10')" + "\n\tAND co.country_id IN ('UK', 'US')\n" + "GROUP BY GROUPING SETS ((channel_desc, calendar_month_desc, co.country_id), (channel_desc, co.country_id), " + "(calendar_month_desc, co.country_id));\n";
    OracleStatementParser parser = new OracleStatementParser(sql);
    SQLSelectStatement stmt = (SQLSelectStatement) parser.parseStatementList().get(0);
    String text = TestUtils.outputOracle(stmt);
    Assert.assertEquals(expected, text);
    System.out.println(text);
}

Example 58 with SQLSelectStatement

use of com.alibaba.druid.sql.ast.statement.SQLSelectStatement in project druid by alibaba.

the class CursorTest method test_cursor.

public void test_cursor() throws Exception {
    String sql = "SELECT department_name, CURSOR(SELECT salary, commission_pct FROM employees e WHERE e.department_id = d.department_id) " + "FROM departments d;";
    OracleStatementParser parser = new OracleStatementParser(sql);
    SQLSelectStatement stmt = (SQLSelectStatement) parser.parseStatementList().get(0);
    String text = TestUtils.outputOracle(stmt);
    Assert.assertEquals("SELECT department_name, CURSOR(\n\t\tSELECT salary, commission_pct\n" + "\t\tFROM employees e\n" + "\t\tWHERE e.department_id = d.department_id\n\t)\n" + "FROM departments d;\n", text);
    System.out.println(text);
}

Example 59 with SQLSelectStatement

use of com.alibaba.druid.sql.ast.statement.SQLSelectStatement in project druid by alibaba.

the class LargeOrTest method test_largeAnd.

public void test_largeAnd() throws Exception {
    StringBuffer buf = new StringBuffer();
    buf.append("SELECT 1 FROM T WHERE ID = ?");
    for (int i = 0; i < 10000; ++i) {
        buf.append(" AND ID = ?");
    }
    String sql = buf.toString();
    OracleStatementParser parser = new OracleStatementParser(sql);
    SQLSelectStatement stmt = (SQLSelectStatement) parser.parseStatementList().get(0);
    SQLSelectQueryBlock select = (SQLSelectQueryBlock) stmt.getSelect().getQuery();
    SQLBinaryOpExpr where = (SQLBinaryOpExpr) select.getWhere();
    SQLBinaryOpExpr last = (SQLBinaryOpExpr) where.getRight();
    Assert.assertEquals(SQLBinaryOperator.Equality, last.getOperator());
}

Example 60 with SQLSelectStatement

use of com.alibaba.druid.sql.ast.statement.SQLSelectStatement in project druid by alibaba.

the class OracleAnalyticTest method test_0.

public void test_0() throws Exception {
    String sql = "SELECT last_name, salary, STDDEV(salary) OVER (ORDER BY hire_date) \"StdDev\" " + "FROM employees " + "WHERE department_id = 30;";
    String expect = "SELECT last_name, salary, STDDEV(salary) OVER (ORDER BY hire_date) AS \"StdDev\"\n" + "FROM employees\n" + "WHERE department_id = 30;\n";
    OracleStatementParser parser = new OracleStatementParser(sql);
    SQLSelectStatement stmt = (SQLSelectStatement) parser.parseStatementList().get(0);
    String text = TestUtils.outputOracle(stmt);
    Assert.assertEquals(expect, text);
    System.out.println(text);
}