Hi Linda, hi Mike,
I have two questions about the HAVING clause in EJBQL.
(1) Aggregates in HAVING clause
I read the current EJBQL BNF that is does not allow aggregates in the
HAVING clause. I'm wondering whether this is on purpose, because the
following query would not be valid:
SELECT c.country, COUNT(c) FROM Customer c
GROUP by c.country HAVING COUNT(c.country) > 3
The BNF for the HAVING clause is (see section 4.7 GROUP BY, HAVING on
page 79 and the BNF on page 92):
having_clause ::= HAVING conditional_expression
Only select_expression, simple_select_expression and constructor_item
allow aggregates and none of them is reachable from conditional_expression.
(2) HAVING clause w/o GROUP BY clause
The spec explicitly allows a HAVING query w/o GROUP BY. Are there any
SELECT clause restrictions in this case?
I have the feeling that there are issues with mapping such queries to
SQL. I tried to run SQL HAVING queries w/o GROUP BY on Oracle and Derby,
but did not have success. Oracle returns an error message "ORA-00979:
not a GROUP BY expression". MySql seems to accept HAVING w/o GROUP BY,
but only if the SELECT clause only uses columns from the HAVING clause.
Thanks.
Regards Michael