Scalar Subquery in GROUP BY and HAVING clause

From: <tncc>
Date: Mon, 20 Dec 2004 17:20:40 +0800
Message-ID: <41c69969_1@rain.i-cable.com>

According to Oracle's documentation (SQL reference), GROUP BY and HAVING clause cannot have scalar subquery. However, I tested with Oracle 9.2 personal edition and found that the following query can be executed without error. The query does contain a scalar subquery in its HAVING clause. This query is actually taken from Oreilly's Mastering Oracle SQL 2nd edition chapter 5 section 5.2.1.

SELECT sales_emp_id, COUNT(*)
FROM cust_order
GROUP BY sales_emp_id
HAVING COUNT(*) = (SELECT MAX(COUNT(*))

                   FROM cust_order
                   GROUP BY sales_emp_id);

Can anyone help me to explain the discrepancy? Received on Mon Dec 20 2004 - 03:20:40 CST