SELECT - HAVING
... HAVING sql_cond ...
HAVING limits the number of rows in groups in the results set
by using the logical expression
sql_cond on these rows. The syntax of the
sql_cond matches the syntax of the logical expression
sql_cond of the
WHERE condition. The logical expression evaluates the content of row groups.
The operands of the relational expressions of the logical expressions can be as follows:
- Host variables on the right side of comparisons.
Columns of the database tables or views specified after
FROM on both sides of the comparisons. These columns do not need to be specified as columns
SELECTlist, but must be specified after the addition
GROUP BY. For each group, the value used for the grouping is evaluated.
Any aggregate expressions on both sides of the comparisons. Any columns and
SQL expressions constructed using the columns can be specified
as arguments of the aggregate functions. These columns do not need to be specified after GROUP
BY. This kind of aggregate expression is evaluated for each row group defined in GROUP
BY and its result is used as an operand in the comparison. The same applies to the SQL expressions
in the aggregate functions as in the
SELECTlist. The aggregate functions of the
SELECTlist and the
SELECTclause and the SQL expressions specified as arguments here do not need to be the same.
If no GROUP
BY grouping is applied to columns specified after
HAVINGoutside of aggregate functions, a syntax error occurs in the strict modes of the syntax check from Release 7.40, SP08. Outside of these strict modes, a syntax check warning is produced and a non-catchable exception is raised. The same applies to columns specified directly in the
SELECTlist when a
HAVINGclause is specified, but that are not specified after
Columns that are specified as arguments of aggregate functions after
HAVINGcan also be specified after
If the addition
GROUP BYis not specified or the data object
column_syntaxin the dynamic column specification after
GROUP BYis initial, the addition
HAVINGcan only be specified if the entire results set is grouped into a line (that is, if there are only aggregate expressions specified after
SELECT). In this case, only aggregate expressions can be specified as operands in
sql_cond. These operands are evaluated for all rows in the results set.
Reads the number of booked smoking and non-smoking seats for each flight date of a particular flight connection.
PARAMETERS: p_carrid TYPE sbook-carrid, p_connid TYPE sbook-connid. TYPES: BEGIN OF sbook_type, fldate TYPE sbook-fldate, smoker TYPE sbook-smoker, smk_cnt TYPE i, END OF sbook_type. DATA sbook_tab TYPE TABLE OF sbook_type. SELECT fldate, smoker, COUNT( * ) AS smk_cnt FROM sbook WHERE connid = @p_connid GROUP BY carrid, fldate, smoker HAVING carrid = @p_carrid ORDER BY fldate, smoker INTO CORRESPONDING FIELDS OF TABLE @sbook_tab.