Click here to view and discuss this page in DocCommentXchange. In the future, you will be sent there automatically.

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( DepartmentID, amt ) AS
    ( SELECT DepartmentID, SUM( Salary ) AS amt
      FROM Employees GROUP BY DepartmentID )
  SELECT DepartmentID, 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 ( DepartmentID, Size, pay ) AS
     WITH
       CountEmployees( DepartmentID, n ) AS
         ( SELECT DepartmentID, count(*) AS n
           FROM Employees GROUP BY DepartmentID ),
       DeptPayroll( DepartmentID, amt ) AS
         ( SELECT DepartmentID, SUM( Salary ) AS amt
           FROM Employees GROUP BY DepartmentID )
    SELECT count.DepartmentID, count.n, pay.amt
    FROM CountEmployees count JOIN DeptPayroll pay
    ON count.DepartmentID = pay.DepartmentID
    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.

  CREATE TABLE LargestPayrolls ( DepartmentID INTEGER, Payroll NUMERIC, CurrentDate DATE );
  INSERT INTO LargestPayrolls( DepartmentID, Payroll, CurrentDate )
    WITH DeptPayroll( DepartmentID, amt ) AS
      ( SELECT DepartmentID, SUM( Salary ) AS amt
        FROM Employees
        GROUP BY DepartmentID )
    SELECT DepartmentID, amt, CURRENT TIMESTAMP
    FROM DeptPayroll
    WHERE amt = ( SELECT MAX( amt )
                  FROM DeptPayroll );