Skip to content

ABAP Keyword Documentation →  ABAP − Reference →  Processing External Data →  ABAP Database Access →  ABAP SQL →  ABAP SQL - Reads →  SELECT clauses 

SELECT - HAVING

Quick 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 of a query by using the logical expression sql_cond on these rows. The logical expression evaluates the content of row groups. Those rows are placed in the results set for which the logical expression is true.

The following should be noted with respected to the operands of the relational expressions of the logical expression sql_cond:

  • Non-aggregated columns used as operands in the data sources specified after FROM do not need to be specified in the SELECT list and must be specified after the addition GROUP BY instead. For each group, the value used for the grouping is evaluated.
  • If SQL expressions are used on the left side, either the same expression must be specified or all non-aggregated columns of the expression must be specified individually after GROUP BY.
  • In a HAVING condition, aggregate expressions sql_agg can be used directly or as operands of SQL expressions on both sides of comparisons. Columns that are specified as the argument of an aggregate expression directly or via an SQL expression do not need to be listed 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 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 expressions here must be specified after GROUP BY. If the SELECT list is specified as *, HAVING clauses without GROUP BY clauses cannot be used.

Implicit ABAP SQL client handling applies to the HAVING clause. The client column of a client-specific data source cannot be used as an operand in the HAVING condition.


Notes

  • Aggregate expressions on the left side of a logical expression after HAVING are SQL expressions. Thus, all SQL expressions except for window expressions can be specified there. Aggregate expressions on the right side of a logical expression are an exception to the rule that no SQL expressions can be specified here.
  • Columns that are specified as arguments of aggregate expressions after HAVING can also be specified after GROUP BY.
  • If a column is specified using a path expression, both the parameter passing and any attributes specified are respected by comparisons between the SELECT list and the HAVING clause.
  • 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.
  • The client column of a client-specific data source can still be used in the HAVING condition if implicit client handling is switched off using the obsolete addition CLIENT SPECIFIED.
  • 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.


Example

Reads the number of bookings in each class and for each flight date of a connection.

DATA: carrid TYPE sbook-carrid VALUE 'LH', 
      connid TYPE sbook-connid VALUE '400'. 

cl_demo_input=>new( 
  )->add_field( CHANGING field = carrid 
  )->add_field( CHANGING field = connid )->request( ). 

SELECT fldate, class, COUNT( * ) AS class_cnt 
       FROM sbook 
       WHERE connid = @connid 
       GROUP BY carrid, fldate, class 
       HAVING carrid = @carrid 
       ORDER BY fldate, class 
       INTO TABLE @DATA(result). 

cl_demo_output=>display( result ).

Executable Example

SQL Expressions, Use in Aggregate Expressions