private void removeObjectFromGroupBy(TCustomSqlStatement stmt, TObjectName column) { if (!(stmt is TSelectSqlStatement)) { return; } TSelectSqlStatement select = (TSelectSqlStatement)stmt; TGroupBy groupBy = select.GroupByClause; if (groupBy == null) { return; } for (int i = 0; i < groupBy.Items.Count; i++) { TGroupByItem item = groupBy.Items.getGroupByItem(i); if (item.startToken.posinlist <= column.startToken.posinlist && item.endToken.posinlist >= column.endToken.posinlist) { groupBy.Items.removeGroupByItem(i); break; } } if (groupBy.Items.Count == 0) { select.GroupByClause = null; } }
public virtual void testRemoveHavingClause() { parser.sqltext = "SELECT\n" + "c.ID AS \"SMS.ID\"\n" + "FROM\n" + "SUMMIT.cntrb_detail c\n" + "where\n" + "c.cntrb_date >='$GivingFromDate$'\n" + "and c.cntrb_date<='$GivingThruDate$'\n" + "group by c.id\n" + "having sum(c.amt) >= '$GivingFromAmount$' and sum(c.amt) <= '$GivingThruAmount$'"; Assert.IsTrue(parser.parse() == 0); TSelectSqlStatement selectSqlStatement = (TSelectSqlStatement)parser.sqlstatements.get(0); TGroupBy groupBy = selectSqlStatement.GroupByClause; groupBy.HavingClause = null; Assert.IsTrue(testScriptGenerator.verifyScript(EDbVendor.dbvoracle , selectSqlStatement.ToScript() , "SELECT c.ID AS \"SMS.ID\"\n" + "FROM summit.cntrb_detail c\n" + "WHERE c.cntrb_date >= '$GivingFromDate$'\n" + " AND c.cntrb_date <= '$GivingThruDate$'\n" + "GROUP BY c.ID" )); }
private void removeObjectFromHavingClause(TCustomSqlStatement stmt, TObjectName column) { if (!(stmt is TSelectSqlStatement)) { return; } TSelectSqlStatement select = (TSelectSqlStatement)stmt; TGroupBy groupBy = select.GroupByClause; if (groupBy == null) { return; } TExpression expression = groupBy.HavingClause; removeObjectFromExpression(expression, column); if (expression.ToScript().Trim().Length == 0) { groupBy.HavingClause = null; } }