To use multiple with statement by union all

Question

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

Answer 1

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