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 ); |
Discuss this page in DocCommentXchange. Send feedback about this page using email. |
Copyright © 2009, iAnywhere Solutions, Inc. - SQL Anywhere 11.0.1 |