1

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

5

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
| 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

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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