SQL Server: Combine several SELECT statements with "WITH" part into a UNION

Question

I have several of the statements like this (that use WITH statement):

WITH valDiff AS (SELECT <ComplexClause1> AS v1 FROM [MyTable] WHERE <OtherClause1>) SELECT SUM(CASE WHEN v1 < @MaxVal THEN v1 ELSE @MaxVal END) FROM valDiff

UNION

WITH valDiff AS (SELECT <ComplexClauseN> AS v1 FROM [MyTable] WHERE <OtherClauseN>) SELECT SUM(CASE WHEN v1 < @MaxVal THEN v1 ELSE @MaxVal END) FROM valDiff

I need to incorporate them into a union so that the result is returned "in one swoop". Those statements work fine by themselves, but if I add word "UNION" between them, like I showed above, I get the following error:

Incorrect syntax near the keyword 'UNION'.
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."

What am I doing wrong?

Answer 1

The WITH will span all clauses in the UNION

;WITH valDiff AS 
(
whatever
) 
SELECT ... FROM valDiff ...
UNION ALL
SELECT ... FROM valDiff ...

With a different CTE in each clause (as in this case):

;WITH CTE1 AS 
(
whatever
) , CTE2 AS
(
something
)
SELECT ... FROM CTE1 ...
UNION ALL
SELECT ... FROM CTE2 ...

Answer 2

Rearrange your syntax a bit so that all the CTEs come first:

WITH valDiff1 AS (SELECT <ComplexClause1> AS v1 FROM [MyTable] WHERE <OtherClause1>), 
     valDiff2 AS (SELECT <ComplexClauseN> AS v1 FROM [MyTable] WHERE <OtherClauseN>)
SELECT SUM(CASE WHEN v1 < @MaxVal THEN v1 ELSE @MaxVal END) FROM valDiff1
UNION ALL
SELECT SUM(CASE WHEN v1 < @MaxVal THEN v1 ELSE @MaxVal END) FROM valDiff2