SYSTEM AND METHOD FOR PROCESSING QUERIES HAVING AN INNER QUERY BLOCK CONTAINING A GROUPING OPERATOR

Queries having a outer query block enclosing an inner query block, such as a reference to a view or a subquery, with a grouping operator, such as GROUP or DISTINCT, are transformed by merging the inner query block into the outer query block. The FROM clause of the transformed query includes the tables referenced in the FROM clauses of the outer query block and the inner query block. A new GROUP BY clause is created for the outer query block that contains the rowids of tables referenced in the FROM list of the original query and the expressions in the GROUP BY clause of the inner query block. The outer query block can be split if the outer query block also contains a GROUP BY or HAVING clause.