How to Use Common Table Expression [CTE] in DB2

Here is an example and how to use Common Table Expression (CTE) in DB2. How to identify it? The ‘WITH’ Clause in SQL query says that it is a CTE.

CTE the best example SQL query

WITH DEPTOTAL (WORKDEPT, MAXSALARY) AS
(SELECT WORKDEPT, SUM(SALARY+COMM)
  FROM DSN8910.EMP
  GROUP BY WORKDEPT)
SELECT WORKDEPT FROM DEPTOTAL
WHERE MAXSALARY =
  (SELECT MAX(MAXSALARY)
          FROM DEPTOTAL);

Here, the derived value is ‘WORKDEPT’ from the WITH table ‘DEPTOTAL‘.

3 Steps How CTE SQL Works

Step-1

Get MAXSALARY.

Step-2

Apply expression SUM(SALARY+COMM), and select the rows.

Step-3

You will get desired Result.