Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

With inside with

This is ok

    WITH CS AS (
      SELECT      'ab' as a
    )
    SELECT * from cs

http://data.stackexchange.com/mathematics/revision/138658/170321/comment-partition-by-question

But, as soon as I wrap it with a second with,

with PS as (
        WITH CS AS (
          SELECT      'ab' as a
        )
        SELECT * from cs
        ) select * from PS

I get this error,

Incorrect syntax near the keyword 'WITH'. Incorrect syntax near the keyword 'with'. If this statement is a common table expression, an xmlnamespaces clause or a change tracking context clause, the previous statement must be terminated with a semicolon. Incorrect syntax near ')'.

With inside select

The following is ok

select ab.a from (
  select a from (SELECT 'ab' as a) b
            ) ab

This is also runs

  with cs as (SELECT 'ab' as a)
  select a from cs

But, wrapping expression with another select, as this one proposes,

  select a from (
    with cs as (SELECT 'ab' as a)
    select a from cs
  ) ab

fails again with the same error.

I do it in data.stackexchange and they report that they use MSSQL

share|improve this question

1 Answer 1

up vote 5 down vote accepted

From documentation

Specifying more than one WITH clause in a CTE is not allowed. For example, if a CTE_query_definition contains a subquery, that subquery cannot contain a nested WITH clause that defines another CTE.

Thus,

with PS as (
    WITH CS AS (
      SELECT      'ab' as a
    )
    SELECT * from cs
    ) select * from PS

gives you error. You can define multiple CTEs though :

WITH CS AS (SELECT 'ab' as a),
PS as (SELECT * from cs) 
select * from PS
share|improve this answer
    
Oops, sorry my bad... fixing. Second WITH is redundant. It should work now .. Check updated answer –  a1ex07 Sep 22 '13 at 14:16

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.