CTE dentro de un CTE

Question

Sep 10, 2013, 10:47 PM

CTE dentro de un CTE

¿Es posible escribir un CTE dentro de un CTE?

Quiero que siga esta lógica, pero al intérprete no le gusta este código.

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