HAVING Clause
The rules and restrictions are:
You cannot specify LOB columns in the HAVING search condition.
The conditional expression can define one or more aggregates (for example, MAX, MIN,
AVG, SUM, COUNT) and can be applied to the rows of the following group conditions:
A single group defined in the SELECT expression list, which has only aggregate results
One or more groups defined in a GROUP BY clause
HAVING cannot be specified in a recursive statement of a recursive query; however,
a nonrecursive seed statement in a recursive query can specify the HAVING clause.
If you specify the value for a row‑level security constraint in a search condition,
that value must be expressed in its encoded form.
You can specify a scalar subquery as an operand of a scalar predicate in the HAVING
clause of a query.
The following rules apply to the use of the DEFAULT function as part of the search
condition within a HAVING clause:
You can specify a DEFAULT function with a column name argument within a predicate.
The system evaluates the DEFAULT function to the default value of the column specified
as its argument. Once the system has evaluated the DEFAULT function, it treats it
like a constant in the predicate.
You can specify a DEFAULT function without a column name argument within a predicate
only if there is one column specification and one DEFAULT function as the terms on
each side of the comparison operator within the expression.
Following existing comparison rules, a condition with a DEFAULT function used with
comparison operators other than IS NULL or IS NOT NULL is unknown if the DEFAULT function
evaluates to null.
A condition other than IS NULL or IS NOT NULL with a DEFAULT function compared with
a null evaluates to unknown.
IF a DEFAULT function is used with …
|
THEN the comparison is …
|
IS NULL
|
TRUE if the default is null
Else it is FALSE
|
IS NOT NULL
|
FALSE if the default is null
Else it is TRUE
|
See SQL Functions, Operators, Expressions, and Predicates for more information about the DEFAULT function.
You cannot specify a SAMPLE clause in a subquery used as a HAVING clause predicate.