SAP NetWeaver AS ABAP Release 750, ©Copyright 2016 SAP AG. All rights reserved.
ABAP - Keyword Documentation →
ABAP - Reference →
Processing External Data →
ABAP Database Accesses →
Open SQL →
Open SQL - Read Accesses →
SELECT - HAVING
... HAVING sql_cond ...
The addition 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
logical expression 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 and
host expressions on the right sides of comparisons.
Columns of the
data sources specified after
FROM on both sides of the comparisons. These columns do not need to be
specified as columns in the SELECT
list, but must be specified after the addition GROUP BY. For each group, the value used for the grouping is evaluated.
SQL expressions on the left sides of comparisons. Either
the same expression must be specified after GROUP BY or all columns of the expression individually.
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 SELECT list. The aggregate functions
of the SELECT list and the
HAVING clause and the SQL expressions specified as arguments here do not need to be the same.
If a HAVING clause is specified, all columns in the SELECT list that are not arguments of
aggregate functions here must be specified after
GROUP BY. If the
SELECT list is specified as *, HAVING
clauses without GROUP BY clauses cannot be used. The addition HAVING cannot be specified for
pooled tables and cluster tables.
If no GROUP
BY grouping is applied to columns specified after HAVING outside 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
SELECT list when a HAVING clause is specified, but that are not specified after GROUP BY.
If the SELECT list is specified
as * and a HAVING clause is used without a GROUP BY clause, a syntax error is raised in
strict mode from Release 7.50.
When an SQL expression is specified on the left side of the HAVING condition or a host expression occurs on the right side, the syntax check is performed in
strict mode from Release 7.50, which handles the statement more strictly than the regular syntax check.
Columns that are specified as arguments of aggregate functions after HAVING can also be specified after GROUP BY.
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 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
WHERE connid = @p_connid
GROUP BY carrid, fldate, smoker
HAVING carrid = @p_carrid
ORDER BY fldate, smoker
INTO CORRESPONDING FIELDS OF TABLE @sbook_tab.
See SQL Expressions, Use in Aggregate Expressions.