sqlsql-serverunion-all

How to use multiple with statements along with UNION ALL in SQL?

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

Solution

  • The syntax is

    With l1 ( a ) as ( Select ... )
   , l2 ( a ) as ( ... )
  Select ... From ...
  Union
  Select ... From ...