Skip to content

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

SELECT - HAVING

Short Reference

Other versions: 7.31 | 7.40 | 7.54

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 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 in the SELECT list, 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 SELECT list. The aggregate functions of the SELECT list and the SELECT 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.


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.
  • 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.