SELECT - HAVING - ABAP Keyword Documentation

ABAP - Keyword Documentation → ABAP - Reference → Processing External Data → ABAP Database Accesses → Open SQL → Open SQL - Read Accesses → SELECT →

SELECT - HAVING

Quick Reference

Syntax

... HAVING sql_cond ...

Effect

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.

Notes

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.

Example

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.

Example

See SQL Expressions, Use in Aggregate Expressions.