views:

23

answers:

0

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?