Entering content frame

Selecting Groups of Lines Locate the document in its SAP Library structure

The HAVING clause uses conditions to restrict the number of groups selected.

This graphic is explained in the accompanying text

You can only use the HAVING clause in conjunction with the GROUP-BYclause.

To select line groups, use:

SELECT lines s1 [AS a1] s2 [AS a2]...
             agg sm [AS am] agg sn [AS an]...
...
  GROUP BY s1 s2....

  HAVING cond.

The conditions cond that you can use in the HAVING clause are the same as those in the SELECT clause, with the restrictions that you can only use columns from the SELECT clause, and not all of the columns from the database tables in the FROMclause. If you use an invalid column, a runtime error results.

On the other hand, you can enter aggregate expressions for all columns read from the database table that do not appear in the GROUP-BY clause. This means that you can use aggregate expressions, even if they do not appear in the SELECT clause. You cannot use aggregate expressions in the conditions in the WHERE clause.

As in the WHERE clause, you can specify the conditions in the HAVING clause as the contents of an internal table with line type c and length 72.

Example

REPORT demo_select_group_by_having.

DATA wa TYPE sflight.

SELECT     connid
  INTO     wa-connid
  FROM     sflight
  WHERE    carrid = 'LH'
  GROUP BY connid
  HAVING   SUM( seatsocc ) > 300.

  WRITE: / wa-carrid, wa-connid.

ENDSELECT.

This example selects groups of lines from database table SFLIGHT with the value ‘LH’ for CARRID and identical values of CONNID. The groups are then restricted further by the condition that the sum of the contents of the column SEATSOCC for a group must be greater than 300.

 

 

Leaving content frame