Hi Linda,
> Hi Michael,
>
>
> Michael Bouschen wrote:
>
>> 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.
>>
>
> This is a bug.
>
> The HAVING clause must specify search conditions over the grouping
> columns or aggregate functions that apply to grouping columns.
An option for the BNF is adding aggregates to the non-terminal
arithmetic_primary with a footnote saying that it is only allowed as
part of the having clause 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?
>>
>
> When HAVING is used in SQL without GROUP BY, the entire table is
> treated as a single group, and the select list can only
> consist of aggregate functions (and literals, which EJB QL doesn't
> currently specify for use in the SELECT clause).
Should the spec explicitly spell out the restrictions for the SELECT
clause in case there is a HAVING w/o GROUP BY?
However, I'm not sure whether HAVING w/o GROUP BY adds much value and
I'm a little concerned about some SQL databases not supporting this.
Thanks.
Regards Michael
>
>
>> 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.
>>
>
> Thank you!
>
> -Linda
>
>
>> Regards Michael