SQL Error [156] [S0001]: Incorrect syntax near the keyword 'union'
WITH a AS
(
SELECT
ii.RPTCategory AS Cat,
YEAR(ii.CreateDate) AS CatYear, MONTH(ii.CreateDate) AS CatMonth,
COUNT(*) AS CatNo
FROM
InsInvoice ii
WHERE
ii.RPTCategory IN ('12')
GROUP BY
ii.RPTCategory, YEAR(ii.CreateDate), MONTH(ii.CreateDate)
),
b AS
(
SELECT
ii.Category AS Cat,
YEAR(ii.CreateDate) AS CatYear, MONTH(ii.CreateDate) AS CatMonth,
COUNT(*) AS CatNo
FROM
InsInvoice ii
WHERE
ii.Category IN ('41')
GROUP BY
ii.Category, YEAR(ii.CreateDate), MONTH(ii.CreateDate)
)
SELECT *
FROM
(a UNION b)
I get the error
Incorrect syntax near the keyword union
All searches I've done, the user encounters the error when using order by within their sub select statements but I'm not doing that. I've tried splitting this out into sub select statements instead of using the with clause but I get the same error.