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.

To created a common table expression use one of the following approaches:

• Specify a WITH clause at the beginning of a SELECT statement.
  For example, 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

• Use common table expressions by specifying WITH 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.
  For example, 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 DSN8C10.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

• Use common table expressions by specifying WITH before a fullselect in an INSERT statement.
  For example, 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 DSN8C10.EMP
  		      WHERE job = 'senior engineer'
   		      GROUP BY workdept)
  	  SELECT d.manager
  	     FROM DSN8C10.DEPT d, VITALDEPT s
  	     WHERE d.workdept = s.workdept
  	         AND s.se_count  >  (SELECT  AVG(se_count)
  					                     FROM VITALDEPT);