iAnywhere.com - Where common table expressions are permitted

Where common table expressions are permitted

Common table expression definitions are permitted in only three places, although they may be referenced throughout the body of the query or in any subqueries.

Top-level SELECT statement Common table expressions are permitted within top-level SELECT statements, but not within subqueries.

WITH DeptPayroll( dept_id, amt ) AS
  ( SELECT dept_id, sum(salary) AS amt
    FROM employee GROUP BY dept_id )
SELECT dept_id, amt
FROM DeptPayroll
WHERE amt = ( SELECT max(amt)
              FROM DeptPayroll )

The top-level SELECT statement in a view definition Common table expressions are permitted within the top-level SELECT statement that defines a view, but not within subqueries within the definition.

CREATE VIEW LargestDept (dept_id, size, pay) AS
   WITH
     CountEmployees(dept_id, n) AS
       ( SELECT dept_id, count(*) AS n
         FROM employee GROUP BY dept_id ),
     DeptPayroll( dept_id, amt ) AS
       ( SELECT dept_id, sum(salary) AS amt
         FROM employee GROUP BY dept_id )
  SELECT count.dept_id, count.n, pay.amt
  FROM CountEmployees count JOIN DeptPayroll pay
  ON count.dept_id = pay.dept_id
  WHERE count.n = ( SELECT max(n) FROM CountEmployees )
     OR pay.amt = ( SELECT max(amt) FROM DeptPayroll )

A top-level SELECT statement in an INSERT statement Common table expressions are permitted within a top-level SELECT statement in an INSERT statement, but not within subqueries within the INSERT statement.

INSERT INTO LargestPayrolls (dept_id, payroll, date)
  WITH DeptPayroll( dept_id, amt ) AS
    ( SELECT dept_id, sum(salary) AS amt
      FROM employee
      GROUP BY dept_id )
  SELECT dept_id, amt, CURRENT TIMESTAMP
  FROM DeptPayroll
  WHERE amt = ( SELECT max(amt)
                FROM DeptPayroll )