Geek Answers Handbook

SQL Server: combine multiple SELECT statements with the "WITH" part in UNION

I have several such statements (which use the 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 include them in the union so that the result comes back “in one fell swoop”. These statements work fine on their own, but if I add the word “UNION” between them, as I showed above, I get the following error:

Incorrect syntax next to the keyword "UNION".
Incorrect syntax next to the 'with' keyword.
If this statement is a common table expression, an xmlnamespaces clause, or a change tracking context clause, the previous statement must be interrupted by a semicolon. "

What am I doing wrong?

+5
2

WITH UNION

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

CTE ( ):

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

, CTE:

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

More articles:


All Articles