9
WITH L1 AS
(
   SELECT
)
SELECT A FROM L1

UNION ALL

SELECT A FROM TABLE

UNION ALL

WITH L2 AS
(
   SELECT
)
SELECT A FROM L2

UNION ALL

WITH L3 AS
(
   SELECT
)
SELECT A FROM L3

I get an error

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

Please help

Sanushi Salgado
  • 1,195
  • 1
  • 11
  • 18
vijay kumar
  • 203
  • 1
  • 6
  • 16

3 Answers3

14

You cannot use WITH in the middle of a query expression. WITH is used to build up intermediate queries for use by other queries immediately after (meaning it cannot be used by multiple independent queries).

So you probably want something like:

WITH L1 
AS
(
    SELECT ...
),
L2 AS
(
    SELECT ...
),
L3 AS
(
    SELECT ...
)

// begin final query    
SELECT A FROM L1
UNION ALL
SELECT A FROM TABLE
UNION ALL
SELECT A FROM L2
UNION ALL
SELECT A FROM L3
D Stanley
  • 149,601
  • 11
  • 178
  • 240
7

The syntax is

With l1 ( a ) as ( Select ... )
   , l2 ( a ) as ( ... )
  Select ... From ...
  Union
  Select ... From ...
Lennart - Slava Ukraini
  • 6,936
  • 1
  • 20
  • 32
3

Two ways to do this:

SELECT A FROM L1
UNION ALL
SELECT A FROM L2
UNION ALL
SELECT A FROM L3

OR

WITH CTE_L AS
(
    SELECT A FROM L1
    UNION ALL
    SELECT A FROM L2
    UNION ALL
    SELECT A FROM L3
)
SELECT * FROM CTE_L
Apothis
  • 406
  • 3
  • 7