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.

DB2 Tough Interview Questions.
Useful for interviews and Projects.
$1.00
Interview questions
1.What’s CTE recursion?
It’s not true recursion. Here same table is referenced. The WITH values will be used to get data from other table.
2. CTE Vs Sub-query?
It looks like sub-query. Here the table is same.
Related Posts
References for Recursive
You must be logged in to post a comment.