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 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 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 ), 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 ); |
Discuss this page in DocCommentXchange.
|
Copyright © 2010, iAnywhere Solutions, Inc. - SQL Anywhere 12.0.0 |