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?