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 to be a single group with no grouping columns.

Each column-name in the search condition must satisfy one of the following conditions:

Unambiguously identify a grouping column 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-reference in an outer subselect.

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 Example 6 and Example 7 in Examples of subselect queries.

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, the select list can only include column names when they are arguments to an aggregate function, correlated column references, global variables, host variables, literals, special registers, SQL variables, or SQL parameters.

Note: The following expressions can only be specified in a HAVING clause if they are contained within an aggregate function (SQLSTATE 42803):

ROW CHANGE TIMESTAMP FOR table-designator
ROW CHANGE TOKEN FOR table-designator
RID_BIT or RID scalar function