... HAVING sql_cond ... .
The addition HAVING limits the nunber of lines to be grouped into groups in the resulting set by a logical expression sql_cond for these lines. The syntax of the logical expression sql_cond corresponds to the syntax of the logical expression sql_cond of the WHERE condition. The comparisons of the logical expression evaluate the contents of line groups.
If a grouping is done using the addition GROUP BY, all the columns that are specified in the condition sql_cond directly through their name col will be listed after GROUP BY. The direct specification of different columns leads to an exception CX_SY_OPEN_SQL_DB that can be handled. For any columns in the data base tables or Views listed after FROM, arbitrary aggregate expressions can be specified in the listed database tables in the comparisons of sql_cond. This kind of aggregate expression is evaluated for each line group defined in GROUP BY and its result is used as an operand in the comparison. If such a column is also listed simultaneously as an argument of an aggregate function after SELECT, the aggregate expressions after SELECT and after HAVING can be different.
If the addition GROUP BY is not specified or the data object column_syntax in the dynamic column specification after GROUP BY is initial, the addition HAVING can only be specified if the entire resulting set is grouped into a line - that is, if after SELECT you have solely aggregate expressions. In this case, solely aggregate expressions can be specified as operands in sql_cond. These operands are evaluated for all lines in the resulting set.
The use of aggregate expressions as an operand in dyanmic HAVING conditions is possile only as of Release 6.10.
Reading 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
INTO CORRESPONDING FIELDS OF TABLE sbook_tab
WHERE connid = p_connid
GROUP BY carrid fldate smoker
HAVING carrid = p_carrid
ORDER BY fldate smoker.