[ACCEPTED]-Can I use WITH in TSQL twice to filter a result set like my example?-common-table-expression
Accepted answer
I do it all the time:
WITH stuff1 AS (
SELECT name
,startdate
,id
FROM employees
WHERE startdate > 0
)
,stuff2 AS (
SELECT name
,startdate
,id
FROM stuff1
)
SELECT *
FROM stuff2
WHERE id > 10
As far as I can tell, I 5 haven't reached a limit in CTEs.
The only 4 thing you can't do (which would be pretty 3 useful) is reuse CTEs in separate SELECTs:
WITH stuff1 AS (
SELECT name
,startdate
,id
FROM employees
WHERE startdate > 0
)
,stuff2 AS (
SELECT name
,startdate
,id
FROM stuff1
)
SELECT *
FROM stuff2
WHERE id > 10
;
SELECT *
FROM stuff2
WHERE id < 10
Say. Instead 2 you have to copy and paste the entire CTE 1 chain again.
You may be able to us a series of sub-queries. Or 4 sub-queries nested in a CTE.
An example of 3 sub-queries using Northwind:
SELECT * FROM
(SELECT * FROM
(SELECT * FROM dbo.Employees WHERE Country = 'USA') as TableFiltered1
) AS TableFilterd2 WHERE City = 'Seattle'
You can use 2 two CTEs, but maybe not the way you wanted 1 to, see:
http://www.4guysfromrolla.com/webtech/071906-1.shtml
Source:
stackoverflow.com
More Related questions
Cookie Warning
We use cookies to improve the performance of the site. By staying on our site, you agree to the terms of use of cookies.