sqlsql-serverwith-statementunion-all

How to use WITH clause with UNION ALL in SQL Server


I'm trying but I don't know how to combine two SQL statements including the WITH clause with the UNION ALL. In each of the WITH SQL statements the difference is the WHERE clause.

WITH cte AS 
(
    SELECT 
        CMCONTRACTS.CMSERIALNUMBER, CMACTIVITIES.CMID, 
        CMACTIVITIES.CMSTART, CMACTIVITIES.CMFINISH, 
        CMACTIVITIES.CMSTATUSTYPE,
        ROW_NUMBER() OVER (PARTITION BY CMCONTRACTS.CMSERIALNUMBER 
                           ORDER BY CMACTIVITIES.CMFINISH DESC) RN
    FROM 
        CMACTIVITIES
    LEFT JOIN 
        CMCONTRACTS ON CMACTIVITIES.CMCONTRACTID = CMCONTRACTS.CMID
    WHERE 
        CMACTIVITIES.CMSTATUSTYPE = 3
)
SELECT 
    CMID, CMSTART, CMFINISH, CMSERIALNUMBER, CMSTATUSTYPE
FROM 
    cte
WHERE 
    RN = 1

UNION ALL

WITH cte AS 
(
    SELECT 
        CMCONTRACTS.CMSERIALNUMBER, CMACTIVITIES.CMID, 
        CMACTIVITIES.CMSTART, CMACTIVITIES.CMFINISH, 
        CMACTIVITIES.CMSTATUSTYPE,
        ROW_NUMBER() OVER (PARTITION BY CMCONTRACTS.CMSERIALNUMBER 
                           ORDER BY CMACTIVITIES.CMFINISH ASC) RN
    FROM 
        CMACTIVITIES
    LEFT JOIN 
        CMCONTRACTS ON CMACTIVITIES.CMCONTRACTID = CMCONTRACTS.CMID
    WHERE 
        CMACTIVITIES.CMSTATUSTYPE = '2'
)
SELECT 
    CMID, CMSTART, CMFINISH, CMSERIALNUMBER, CMSTATUSTYPE 
    -- GXSTARTDATE, GXENDDATE, GXFORMULA, GXPRLSID
FROM
    cte
WHERE 
    RN = 1

When I run it, I get the following error :

Msg 156, Level 15, State 1, Line 26
Incorrect syntax near the keyword 'WITH'.

Msg 319, Level 15, State 1, Line 26
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.

Running these two separate SQL queries a take the expected result. But I want to take all results from both previous SQL queries including the WITH clause in one query.


Solution

  • You should first make the CTE's like this:

     WITH cte
    AS (SELECT
              CMCONTRACTS.CMSERIALNUMBER,
              CMACTIVITIES.CMID,
              CMACTIVITIES.CMSTART,
              CMACTIVITIES.CMFINISH,
              CMACTIVITIES.CMSTATUSTYPE,
              ROW_NUMBER() OVER (PARTITION BY CMCONTRACTS.CMSERIALNUMBER
                                 ORDER BY CMACTIVITIES.CMFINISH DESC
                                ) RN
        FROM  CMACTIVITIES
              LEFT JOIN CMCONTRACTS ON CMACTIVITIES.CMCONTRACTID = CMCONTRACTS.CMID
        WHERE CMACTIVITIES.CMSTATUSTYPE = 3),
         cte2
    AS (SELECT
              CMCONTRACTS.CMSERIALNUMBER,
              CMACTIVITIES.CMID,
              CMACTIVITIES.CMSTART,
              CMACTIVITIES.CMFINISH,
              CMACTIVITIES.CMSTATUSTYPE,
              ROW_NUMBER() OVER (PARTITION BY CMCONTRACTS.CMSERIALNUMBER
                                 ORDER BY CMACTIVITIES.CMFINISH ASC
                                ) RN
        FROM  CMACTIVITIES
              LEFT JOIN CMCONTRACTS ON CMACTIVITIES.CMCONTRACTID = CMCONTRACTS.CMID
        WHERE CMACTIVITIES.CMSTATUSTYPE = '2')
    SELECT
          CMID,
          CMSTART,
          CMFINISH,
          CMSERIALNUMBER,
          CMSTATUSTYPE
    FROM  cte
    WHERE RN = 1
    UNION ALL
    SELECT
          CMID,
          CMSTART,
          CMFINISH,
          CMSERIALNUMBER,
          CMSTATUSTYPE -- GXSTARTDATE, GXENDDATE, GXFORMULA, GXPRLSID
    FROM  cte2
    WHERE RN = 1;