ASA SQL User's Guide
  Common Table Expressions
    About common table expressions

Where common table expressions are permitted

Common table expressions can be used only in three situations.

• 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 )