Menu
Is it possible to write a CTE within a CTE?
I want it to follow this logic, but the interpreter doesn't like this code.
with outertest as( with test as ( select SRnum, gamenumber, StartOfDistribution, ApplicationNumber from #main where startofdistribution = '2011-06-14 00:00:00.000' and SRnum = '313' --order by SRnum, gamenumber, StartOfDistribution, ApplicationNumber ) select ApplicationNumber ,count(*) as RetailerAppearance from test group by ApplicationNumber having count(*) = 4 ) select count(*) from outertest 
267
Not only can you define multiple CTEs and reference them in a single SELECT statement, but you can also have a CTE that references another CTE. In order to do this all you need to do is define the referenced CTE prior to using it. Here is an example where my first CTE is referenced inside the second CTE definition.
A straightforward question deserves a straightforward answer: yes, you can. Now that you know how to use multiple CTEs, writing a CTE that references another CTE is just a variation of what you've learned. Since it's a variation, I think it's best to show you how to do it using an example you're already familiar with.
Nested CTEsCommon Table Expressions can be also nested. This means having multiple CTEs in the same query where at least one CTE refers to another CTE.
You can't nest CTEs like that in SQL Server but you can use multiple CTEs the following way:
;with test as ( select SRnum, gamenumber, StartOfDistribution, ApplicationNumber from #main where startofdistribution = '2011-06-14 00:00:00.000' and SRnum = '313' --order by SRnum, gamenumber, StartOfDistribution, ApplicationNumber ), outertest as ( select ApplicationNumber ,count(*) as RetailerAppearance from test group by ApplicationNumber having count(*) = 4 ) select count(*) from outertest If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
