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 a query or in any subqueries.

  • Top-level SELECT statement   Common table expressions are permitted within top-level SELECT statements, but not within subqueries. 
    WITH DepartmentPayroll( DepartmentID, amount ) AS
  ( SELECT DepartmentID, SUM( Salary ) AS amount
    FROM Employees GROUP BY DepartmentID )
SELECT DepartmentID, amount
FROM DepartmentPayroll
WHERE amount = ( SELECT MAX( amount )
              FROM DepartmentPayroll );

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


    CREATE VIEW LargestDept ( DepartmentID, Size, pay ) AS
   WITH
     CountEmployees( DepartmentID, n ) AS
       ( SELECT DepartmentID, COUNT( * ) AS n
         FROM Employees GROUP BY DepartmentID ),
     DepartmentPayroll( DepartmentID, amount ) AS
       ( SELECT DepartmentID, SUM( Salary ) AS amount
         FROM Employees GROUP BY DepartmentID )
  SELECT count.DepartmentID, count.n, pay.amount
  FROM CountEmployees count JOIN DepartmentPayroll pay
  ON count.DepartmentID = pay.DepartmentID
  WHERE count.n = ( SELECT MAX( n ) FROM CountEmployees )
     OR pay.amount = ( SELECT MAX( amount ) FROM DepartmentPayroll );

  • 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 DepartmentPayroll( DepartmentID, amount ) AS
    ( SELECT DepartmentID, SUM( Salary ) AS amount
      FROM Employees
      GROUP BY DepartmentID )
  SELECT DepartmentID, amount, CURRENT TIMESTAMP
  FROM DepartmentPayroll
  WHERE amount = ( SELECT MAX( amount )
                FROM DepartmentPayroll );

 See also
Copyright © 2012, iAnywhere Solutions, Inc. - SQL Anywhere 12.0.1