Selecting Groups of Lines
The HAVING clause uses conditions to restrict the number of groups selected.
You can only use the HAVING clause in conjunction with the GROUP BY clause.
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 FROM clause. 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
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.
