having-clause
The HAVING clause specifies an intermediate result table that consists of those groups of R for which the search-condition is true. R is the result of the previous clause of the subselect. If this clause is not GROUP BY, R is considered a single group with no grouping expressions.
Each expression that contains a column-name in the search condition must do one of the following:
- Unambiguously identify a grouping expression of R.
- Be specified within an aggregate function.
- Be a correlated reference. A column-name is a correlated reference if it identifies a column of a table, view, common table expression, or nested table expression identified in an outer subselect.
The RRN, RID, DATAPARTITIONNAME, DATAPARTITIONNUM, DBPARTITIONNAME, DBPARTITIONNUM, and HASHED_VALUE functions cannot be specified in the HAVING clause unless it is within an aggregate function. See Aggregate functions for restrictions that apply to the use of aggregate functions.
A group of R to which the search condition is applied supplies the argument for each aggregate function in the search condition, except for any function whose argument is a correlated reference.
If the search condition contains a subquery, the subquery can be thought of as being executed each time the search condition is applied to a group of R, and the results used in applying the search condition. In actuality, the subquery is executed for each group only if it contains a correlated reference. For an illustration of the difference, see examples 6 and 7 under Examples of a subselect.
A correlated reference to a group of R must either identify a grouping column or be contained within an aggregate function.
When HAVING is used without GROUP BY, any column name in the select list must appear within an aggregate function.
If a collating sequence other than *HEX is in effect when the statement that contains the HAVING clause is executed and if the search-condition contains operands that have SBCS data, mixed data, or Unicode data, the comparison for those predicates is done using weighted values. The weighted values are derived by applying the collating sequence to the operands in the predicate.