| Bug #23156 | WITH clause for SELECT statement | ||
|---|---|---|---|
| Submitted: | 10 Oct 2006 23:49 | Modified: | 7 Nov 10:20 |
| Reporter: | Lee Stigile | ||
| Status: | Duplicate | ||
| Category: | Server: DML | Severity: | S4 (Feature request) |
| Version: | 5.1 | OS: | Microsoft Windows (Windows) |
| Assigned to: | Target Version: | ||
| Tags: | temporary views | ||
| Triage: | Triaged: D5 (Feature request) | ||
[11 Oct 2006 15:18]
Valeriy Kravchuk
Thank you for a reasonable feature request. Not sure that "Temporary view" is a right name, but your example explains what is needed. Same feature exists in Oracle also.
[17 Dec 2007 10:18]
Paul Lautman
Isn't this already covered by CREATE TEMPORARY TABLE
[3 Mar 2008 1:27]
Mat Lavoie
perhaps the example that was given was not the best to illustrate the benefit of the WITH clause. You can use it to perform recursive queries. look for "Creating a recursive common table expression" at this link http://searchsqlserver.techtarget.com/tip/0,289483,sid87_gci1299502,00.html I am running into the problem that it is very difficult to query a directed acyclic graph without this feature. For example getting the children of a node.
[13 May 20:54]
Andrew Barnes
WITH clauses effectively create inline views, but the general idea is to define clauses that will be used two or more times. However there is no harm in defining a WITH clause that will only be used once - it can be easier to read if you are familiar with the format. Oracle has supported with WITH clause (AKA subquery factoring) since 9i R2: http://www.oracle-developer.net/display.php?id=212 MS SQL Server has supported the WITH clause (AKA Common Table Expressions) since v2005: http://msdn.microsoft.com/en-us/library/ms190766(SQL.90).aspx
[7 Nov 10:20]
Valeriy Kravchuk
This is a duplicate of Bug #16244 actually.
Description: Implement a temporary view that exists only for the life of the session. This is useful for simplifying queries. A permanent view could be used, however; it incurs unnecessary overhead if the query only needs to be implemented a few times. This would be helpful in migrating from iAnyWhere ASA to MySQL. How to repeat: customer suggestion Suggested fix: Temporary View: Possible syntax: WITH CountEmployees( DepartmentID, n ) AS ( SELECT DepartmentID, COUNT(*) AS n FROM Employees GROUP BY DepartmentID ) SELECT DepartmentID, n FROM CountEmployees WHERE n = ( SELECT MAX( n ) FROM CountEmployees );