GROUP BY HAVING

Restriction: This topic applies only when the AppMaster Builder AddPack has been installed, and applies only to Windows platforms.

Use the HAVING clause, which acts as a WHERE clause, with GROUP BY to identify or evaluate the groups you want to include. A HAVING clause:

Names a grouping column or column function with its search conditions or qualifications
Can link qualifications
Can test the number of rows found for a group

Target:

SQL

Comments:

Code GROUP BY with DB-DECLARE and DB-PROCESS only.
Use column names or expressions.
Each column name in the SELECT list must be in the GROUP BY statement, and vice versa.
Use the ORDER BY keyword with GROUP BY to sort rows.
Separate each item in the GROUP BY and ORDER BY clauses with a space; commas are optional.
The HAVING clause should directly follow the GROUP BY statement.
To link qualifications, use the Boolean operators AND and OR in the HAVING clause
To test the number of rows found for a group, code the HAVING clause with COUNT--COUNT(*)operator value

Examples:

This statement:

DB-PROCESS REC EMPLOYEE-REC
... DB-PROCESS-ID D2EMP
... EMP-DEPT
... MAX(EMP-SALARY)
... WHERE EMP-NAME NOT NULL
... GROUP BY EMP-DEPT
... ORDER BY EMP-DEPT

Yields this result:

EMP-DEPT    EMP-SALARY
--------    ----------
FIN001      66700.000
FIN201      45250.000
MKT001      72300.000
PAY001      68800.000
PAY002      43500.000
SYS001      75000.000

This statement first groups rows by EMP-DEPT number, then within department by EMP-SEX, and then calculates maximum rates and salaries for each group.

DB-PROCESS REC EMPLOYEE-REC
... DB-PROCESS-ID D2EMP
... EMP-DEPT
... EMP-SEX
... MAX(EMP-RATE)
... MAX(EMP-SALARY)
... WHERE EMP-NAME NOT NULL
... GROUP BY EMP-DEPT,EMP-SEX
... HAVING COUNT(*) > 2
... AND MIN(EMP-RATE) > 3
... ORDER BY EMP-DEPT,EMP-SEX

Resulting in:

EMP-DEPT    EMP-SEX     EMP-RATE    EMP-SALARY
---------    -------    ---------   ----------
FIN001      F           32.067      66700.000
FIN001      M           31.105      64700.000
FIN201      F           19.711      41000.000
FIN201      M           21.754      45250.000
PAY001      F           33.076      68800.000
PAY001      M           31.884      66320.000
SYS001      F           32.692      68000.000
SYS001      M           36.058      75000.000

This statement uses an expression rather than a column name:

DB-PROCESS 
... REC EMSAVING-REC DB-PROCESS-ID ID-DETAIL  
... RIGHT((ADDRESS_1,12),WS-ADDRESS1) AS ADDRESS-RIGHT1  
... PREVIOUS_BALANCE + INTEREST_PAID                    
... SUM(INTEREST_PAID)
... WHERE STATE = 'MD'
... GROUP BY      
... RIGHT(ADDRESS_1,12)
... PREVIOUS_BALANCE + INTEREST_PAID
... SUM(INTEREST_PAID)                   
... ORDER BY      
... PREVIOUS_BALANCE + INTEREST_PAID
... ADDRESS_1

Which produces this generated code:

EXEC SQL DECLARE ID_DETAIL CURSOR
  FOR SELECT
  RIGHT(  ADDRESS_1,12)
      AS ADDRESS-RIGHT1,
  PREVIOUS_BALANCE + INTEREST_PAID,
  SUM( INTEREST_PAID)
  FROM
  MFIFAT.EMSAVINGS
  WHERE
  STATE = 'MD'
  GROUP BY
  RIGHT(ADDRESS_1,12),
  PREVIOUS_BALANCE
  +
  INTEREST_PAID,
  SUM(INTEREST_PAID)
  ORDER BY
  PREVIOUS_BALANCE
  +
  INTEREST_PAID,
  ADDRESS_1
END-EXEC
perform OPEN CURSOR paragraph,
FETCH CURSOR paragraph, and CLOSE CURSOR paragraph