At 08:29 PM 11/04/2008, Nando Dessena wrote:

>Helen,
>
>H> HAVING is not valid except with respect to GROUP BY.
>
>not only is it valid, it's the only way to filter by an aggregate
>expression when you don't have a group by clause.

Erm, well, what you say is an observation of effect, not a rule. A better way of stating the rule is that HAVING is not valid except with respect to the output of an aggregation. Aggregation output is always in a GROUP BY context, even if the group is implicit - as it is when you apply an aggregate expression to an entire table or to a filtered set.

My concern is that people will come to grief if they misapply grouping and aggregation. The example shown by Anderson Farias illustrates one such misunderstanding:

SELECT r0.UID FROM TAB_A r0
WHERE EXISTS (SELECT 1 FROM TAB_B WHERE
r0.UID = AID having COUNT(1) BETWEEN 1 AND 10)

which is not valid, i.e., if COUNT(1) were legal (and it's not) it would always terminate and return true if at least one match were made and would not ever reach the point of returning either true or false if there were more than 10 matches. The engine *should* return an "expression evaluation not supported" on this predicate.

./heLen