DB2 v8 insert with CTE

Question

I need to select from a CTE (common table expression) in DB2 v8 and insert the result into a table. The relevant documentation for v8 is hard to understand at first glance, but for v9 there's a clear example (http://publib.boulder.ibm.com/infocenter/dzichelp/v2r2/index.jsp?topic=/com.ibm.db29.doc.apsg/db2z_createcte.htm):

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

It does not work in v8 though. How should it be written in v8?

Answer 1

Write it like a boss

INSERT INTO vital_mgr 
(
    SELECT d.manager
    FROM SN8910.DEPT AS d
        INNER JOIN 
            (
                SELECT deptno, count(*)
                FROM DSN8910.EMP
                WHERE job = 'senior engineer'
                GROUP BY deptno
            ) AS s (deptno, se_count)
                ON d.deptno = s.deptno
    WHERE s.se_count > (
                            SELECT AVG(se_count)
                            FROM
                                (
                                    SELECT deptno, count(*)
                                    FROM DSN8910.EMP
                                    WHERE job = 'senior engineer'
                                    GROUP BY deptno
                                ) AS VITALDEPT (deptno, se_count)
                       )
);

Answer 2

There's a simple workaround here that allows you to use an INSERT or UPDATE using a conventional WITH statement. This hack will work for INSERT on V8 or greater, and for UPDATE on V9 or greater.

There are other methods for V8 or greater, typically using sub-selects, but I find them to be unpractical due to their complexity.