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.

https://stackoverflow.com/questions/67259491/sql-error-156-s0001-incorrect-syntax-near-the-keyword-union April 26, 2021 at 08:30AM