@Test public void testGroupBy1() { GroupBy gb = new GroupBy(); gb.addSymbol(new ElementSymbol("m.g.e1")); //$NON-NLS-1$ helpTest(gb, "GROUP BY m.g.e1"); //$NON-NLS-1$ }
GroupBy groupBy = new GroupBy(); for (String keyCol : this.getKeyColumnNames()) { groupBy.addSymbol(new ElementSymbol(keyCol, this.groupSymbol));
/** SELECT a FROM m.g GROUP BY b, c HAVING b=5*/ @Test public void testGroupByHaving() { GroupSymbol g = new GroupSymbol("m.g"); //$NON-NLS-1$ From from = new From(); from.addGroup(g); Select select = new Select(); select.addSymbol(new ElementSymbol("a", false)); //$NON-NLS-1$ GroupBy groupBy = new GroupBy(); groupBy.addSymbol(new ElementSymbol("b", false)); //$NON-NLS-1$ groupBy.addSymbol(new ElementSymbol("c", false)); //$NON-NLS-1$ CompareCriteria having = new CompareCriteria(new ElementSymbol("b", false), CompareCriteria.EQ, new Constant(new Integer(5))); //$NON-NLS-1$ Query query = new Query(); query.setSelect(select); query.setFrom(from); query.setGroupBy(groupBy); query.setHaving(having); helpTest("SELECT a FROM m.g GROUP BY b, c HAVING b=5", //$NON-NLS-1$ "SELECT a FROM m.g GROUP BY b, c HAVING b = 5", //$NON-NLS-1$ query); }
@Test public void testQuery6() { Select select = new Select(); select.addSymbol(new MultipleElementSymbol()); From from = new From(); from.addGroup(new GroupSymbol("m.g")); //$NON-NLS-1$ CompareCriteria cc = new CompareCriteria(new ElementSymbol("e1"), CompareCriteria.EQ, new Constant(new Integer(5))); //$NON-NLS-1$ GroupBy groupBy = new GroupBy(); groupBy.addSymbol(new ElementSymbol("e1")); //$NON-NLS-1$ CompareCriteria having = new CompareCriteria(new ElementSymbol("e1"), CompareCriteria.GT, new Constant(new Integer(0))); //$NON-NLS-1$ Query query = new Query(); query.setSelect(select); query.setFrom(from); query.setCriteria(cc); query.setGroupBy(groupBy); query.setHaving(having); helpTest(query, "SELECT * FROM m.g WHERE e1 = 5 GROUP BY e1 HAVING e1 > 0"); //$NON-NLS-1$ }
/** SELECT a FROM m.g GROUP BY b, c */ @Test public void testGroupBy() { GroupSymbol g = new GroupSymbol("m.g"); //$NON-NLS-1$ From from = new From(); from.addGroup(g); Select select = new Select(); select.addSymbol(new ElementSymbol("a", false)); //$NON-NLS-1$ GroupBy groupBy = new GroupBy(); groupBy.addSymbol(new ElementSymbol("b", false)); //$NON-NLS-1$ groupBy.addSymbol(new ElementSymbol("c", false)); //$NON-NLS-1$ Query query = new Query(); query.setSelect(select); query.setFrom(from); query.setGroupBy(groupBy); helpTest("SELECT a FROM m.g GROUP BY b, c", //$NON-NLS-1$ "SELECT a FROM m.g GROUP BY b, c", //$NON-NLS-1$ query); }
@Test public void testGroupByRollup() { GroupSymbol g = new GroupSymbol("m.g"); //$NON-NLS-1$ From from = new From(); from.addGroup(g); Select select = new Select(); select.addSymbol(new ElementSymbol("a", false)); //$NON-NLS-1$ GroupBy groupBy = new GroupBy(); groupBy.setRollup(true); groupBy.addSymbol(new ElementSymbol("b", false)); //$NON-NLS-1$ groupBy.addSymbol(new ElementSymbol("c", false)); //$NON-NLS-1$ Query query = new Query(); query.setSelect(select); query.setFrom(from); query.setGroupBy(groupBy); helpTest("SELECT a FROM m.g GROUP BY rollup(b, c)", //$NON-NLS-1$ "SELECT a FROM m.g GROUP BY ROLLUP(b, c)", //$NON-NLS-1$ query); }
@Test public void testQuery7() { Select select = new Select(); select.addSymbol(new MultipleElementSymbol()); From from = new From(); from.addGroup(new GroupSymbol("m.g")); //$NON-NLS-1$ CompareCriteria cc = new CompareCriteria(new ElementSymbol("e1"), CompareCriteria.EQ, new Constant(new Integer(5))); //$NON-NLS-1$ GroupBy groupBy = new GroupBy(); groupBy.addSymbol(new ElementSymbol("e1")); //$NON-NLS-1$ CompareCriteria having = new CompareCriteria(new ElementSymbol("e1"), CompareCriteria.GT, new Constant(new Integer(0))); //$NON-NLS-1$ OrderBy orderBy = new OrderBy(); orderBy.addVariable(new ElementSymbol("e1")); //$NON-NLS-1$ Query query = new Query(); query.setSelect(select); query.setFrom(from); query.setCriteria(cc); query.setGroupBy(groupBy); query.setHaving(having); query.setOrderBy(orderBy); helpTest(query, "SELECT * FROM m.g WHERE e1 = 5 GROUP BY e1 HAVING e1 > 0 ORDER BY e1"); //$NON-NLS-1$ }
@Test public void testQuery2() { Select select = new Select(); select.addSymbol(new MultipleElementSymbol()); From from = new From(); from.addGroup(new GroupSymbol("m.g")); //$NON-NLS-1$ CompareCriteria cc = new CompareCriteria(new ElementSymbol("e1"), CompareCriteria.EQ, new Constant(new Integer(5))); //$NON-NLS-1$ GroupBy groupBy = new GroupBy(); groupBy.addSymbol(new ElementSymbol("e1")); //$NON-NLS-1$ CompareCriteria having = new CompareCriteria(new ElementSymbol("e1"), CompareCriteria.GT, new Constant(new Integer(0))); //$NON-NLS-1$ OrderBy orderBy = new OrderBy(); orderBy.addVariable(new ElementSymbol("e1")); //$NON-NLS-1$ Query query = new Query(); query.setSelect(select); query.setFrom(from); query.setCriteria(cc); query.setGroupBy(groupBy); query.setHaving(having); query.setOrderBy(orderBy); helpTest(query, "SELECT * FROM m.g WHERE e1 = 5 GROUP BY e1 HAVING e1 > 0 ORDER BY e1"); //$NON-NLS-1$ }
@Test public void testQuery5() { Select select = new Select(); select.addSymbol(new MultipleElementSymbol()); From from = new From(); from.addGroup(new GroupSymbol("m.g")); //$NON-NLS-1$ CompareCriteria cc = new CompareCriteria(new ElementSymbol("e1"), CompareCriteria.EQ, new Constant(new Integer(5))); //$NON-NLS-1$ GroupBy groupBy = new GroupBy(); groupBy.addSymbol(new ElementSymbol("e1")); //$NON-NLS-1$ OrderBy orderBy = new OrderBy(); orderBy.addVariable(new ElementSymbol("e1")); //$NON-NLS-1$ Query query = new Query(); query.setSelect(select); query.setFrom(from); query.setCriteria(cc); query.setGroupBy(groupBy); query.setOrderBy(orderBy); helpTest(query, "SELECT * FROM m.g WHERE e1 = 5 GROUP BY e1 ORDER BY e1"); //$NON-NLS-1$ }
/** SELECT a FROM m.g GROUP BY a HAVING COUNT(b) > 0*/ @Test public void testHavingFunction() { GroupSymbol g = new GroupSymbol("m.g"); //$NON-NLS-1$ From from = new From(); from.addGroup(g); Select select = new Select(); select.addSymbol(new ElementSymbol("a")); //$NON-NLS-1$ GroupBy groupBy = new GroupBy(); groupBy.addSymbol(new ElementSymbol("a")); //$NON-NLS-1$ Criteria having = new CompareCriteria( new AggregateSymbol("COUNT", false, new ElementSymbol("b", false)), //$NON-NLS-1$ //$NON-NLS-2$ //$NON-NLS-3$ CompareCriteria.GT, new Constant(new Integer(0)) ); Query query = new Query(); query.setSelect(select); query.setFrom(from); query.setGroupBy(groupBy); query.setHaving(having); helpTest("SELECT a FROM m.g GROUP BY a HAVING COUNT(b) > 0", //$NON-NLS-1$ "SELECT a FROM m.g GROUP BY a HAVING COUNT(b) > 0", //$NON-NLS-1$ query); }
@Test public void testQuery3() { Select select = new Select(); select.addSymbol(new MultipleElementSymbol()); From from = new From(); from.addGroup(new GroupSymbol("m.g")); //$NON-NLS-1$ GroupBy groupBy = new GroupBy(); groupBy.addSymbol(new ElementSymbol("e1")); //$NON-NLS-1$ CompareCriteria having = new CompareCriteria(new ElementSymbol("e1"), CompareCriteria.GT, new Constant(new Integer(0))); //$NON-NLS-1$ OrderBy orderBy = new OrderBy(); orderBy.addVariable(new ElementSymbol("e1")); //$NON-NLS-1$ Query query = new Query(); query.setSelect(select); query.setFrom(from); query.setGroupBy(groupBy); query.setHaving(having); query.setOrderBy(orderBy); helpTest(query, "SELECT * FROM m.g GROUP BY e1 HAVING e1 > 0 ORDER BY e1"); //$NON-NLS-1$ }