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
Apply expression SUM(SALARY+COMM), and select the rows.
You will get desired Result.
DB2 Tough Interview Questions.
Useful for interviews and Projects.
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.
References for Recursive
You must be logged in to post a comment.