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

SQL Anywhere 12.0.1 » SQL Anywhere Server - SQL Usage » Query and modify data » Common table expressions

 

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