Creating a common table expression

Creating a common table expression saves you the overhead of creating and dropping a regular view that you need to use only once. Also, during statement preparation, DB2® does not need to access the catalog for the view, which saves you additional overhead.

About this task

Use the WITH clause to create a common table expression.

You can use a common table expression in a SELECT statement by using the WITH clause at the beginning of the statement.

Example: WITH clause in a SELECT statement: The following statement finds the department with the highest total pay. The query involves two levels of aggregation. First, you need to determine the total pay for each department by using the SUM function and order the results by using the GROUP BY clause. You then need to find the department with highest total pay based on the total pay for each department.

WITH DTOTAL (workdept, totalpay) AS
		(SELECT deptno, sum(salary+bonus)
		    FROM  DSN8810.EMP
		    GROUP BY workdept)
	SELECT workdept 
      FROM DTOTAL
	   WHERE totalpay = (SELECT max(totalpay)
			                   FROM  DTOTAL);

The result table for the common table expression, DTOTAL, contains the department number and total pay for each department in the employee table. The fullselect in the previous example uses the result table for DTOTAL to find the department with the highest total pay. The result table for the entire statement looks similar to the following results:

WORKDEPT
======
D11

Using common table expressions with views:

You can use common table expressions before a fullselect in a CREATE VIEW statement. This technique is useful if you need to use the results of a common table expression in more than one query.

Example: Using a WITH clause in a CREATE VIEW statement: The following statement finds the departments that have a greater-than-average total pay and saves the results as the view RICH_DEPT:

CREATE VIEW RICH_DEPT (workdept) AS
	     WITH DTOTAL (workdept, totalpay) AS
		  (SELECT workdept, sum(salary+bonus)
		      FROM DSN8A10.EMP
		      GROUP BY workdept)
	     SELECT workdept
           FROM DTOTAL
	        WHERE totalpay > (SELECT AVG(totalpay)
			                        FROM DTOTAL);

The fullselect in the previous example uses the result table for DTOTAL to find the departments that have a greater-than-average total pay. The result table is saved as the RICH_DEPT view and looks similar to the following results:

WORKDEPT
======
A00
D11
D21

Using common table expressions when you use INSERT:

You can use common table expressions before a fullselect in an INSERT statement.

Example: Using a common table expression in an INSERT statement: The following statement uses the result table for VITALDEPT to find the manager's number for each department that has a greater-than-average number of senior engineers. Each manager's number is then inserted into the vital_mgr table.

INSERT INTO vital_mgr (mgrno)
	  WITH VITALDEPT (workdept, se_count)  AS
		  (SELECT workdept, count(*)
		      FROM DSN8A10.EMP
		      WHERE job = 'senior engineer'
 		      GROUP BY workdept)
	  SELECT d.manager
	     FROM DSN8A10.DEPT d, VITALDEPT s
	     WHERE d.workdept = s.workdept
	         AND s.se_count  >  (SELECT  AVG(se_count)
					                     FROM VITALDEPT);

Parent topic: Creating and modifying DB2 objects from application programs