How to use WITH clause with UNION ALL in SQL Server

Question

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.

You can't declare a CTE (it's not called a "WITH clause") inside a CTE. If you want multiple CTEs the syntax is `WITH CTE1 AS (...), CTE2 AS(...) — Thom A, Jan 26 '23 at 09:11
@larnu, actually the ISO/ANSI SQL standard term is "WITH clause". See ISO/IEC 9075-2:2016(E), section 7.17 : ` ::= WITH [ RECURSIVE ] `. (Optional) feature T121, WITH (excluding RECURSIVE) in query expression. — jarlh, Jan 26 '23 at 09:16
WITH CTTE AS (SELECT ... UNION SELECT 2 ... UNION SELECT 3 ..) — Bogdan Sahlean, Jan 26 '23 at 09:19

Peter · Accepted Answer · 2023-01-26T09:28:02.163

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;

Thanks a lot for your help ! It seems that it returns the expected result ! — Sternahirundo, Jan 26 '23 at 20:46

Charlieface · Answer 2 · 2023-01-26T11:16:19.853

0

In this particular instance you don't need two CTEs, just add CMACTIVITIES.CMSTATUSTYPE to the PARTITION BY clause.

WITH cte AS (
    SELECT
          c.CMSERIALNUMBER,
          a.CMID,
          a.CMSTART,
          a.CMFINISH,
          a.CMSTATUSTYPE,
          ROW_NUMBER() OVER (PARTITION BY c.CMSERIALNUMBER, a.CMSTATUSTYPE
              ORDER BY
                  CASE WHEN a.CMSTATUSTYPE = 2 THEN a.CMFINISH END ASC,
                  CASE WHEN a.CMSTATUSTYPE = 3 THEN a.CMFINISH END DESC
                            ) RN
    FROM  CMACTIVITIES a
    LEFT JOIN CMCONTRACTS c ON a.CMCONTRACTID = c.CMID
    WHERE a.CMSTATUSTYPE IN (2, 3)
)
SELECT
      CMID,
      CMSTART,
      CMFINISH,
      CMSERIALNUMBER,
      CMSTATUSTYPE
FROM  cte
WHERE RN = 1;

It's unclear if CMSTATUSTYPE is a string or a number. You should stick to the one the column is defined as.

edited Jan 26 '23 at 11:16

answered Jan 26 '23 at 10:07

Charlieface

52,284
6
19
43

You missed that the sort direction changes. Still possible to do it in one though and without two sorts – Martin Smith Jan 26 '23 at 10:42
Hmm, that's a knotty one. I guess `ORDER BY CASE WHEN a.CMSTATUSTYPE = 2 THEN a.CMFINISH END, CASE WHEN a.CMSTATUSTYPE = 3 THEN a.CMFINISH END DESC` but then you can't use indexes – Charlieface Jan 26 '23 at 11:09
I'd be minded to use `LAG`/`LEAD` with an ascending order by so it can use an index and only requires one sort direction and determine the first and last member of the partition that way. Which is why I asked if CMFINISH is nullable. Could also do something nastier with a windowed MAX/MIN of a concatenated value – Martin Smith Jan 26 '23 at 11:20
Sounds interesting, not sure how you can do it without an extra sort. Want to post? If you need the `NULL` to find the first row you can also use the `default` parameter on `LEAD` `LAG` instead. – Charlieface Jan 26 '23 at 11:27
Oh I see, `WHERE LagValue IS NULL AND CMSTATUSTYPE = 2 OR LeadValue IS NULL AND CMSTATUSTYPE = 3` very clever. Feel free to edit my answer or post your own. – Charlieface Jan 26 '23 at 11:29

score 0 · Answer 3 · answered Jan 28 '23 at 17:22

The answer you accepted is not very DRY.

The two branches of the CTE are pretty much identical except for filtering on different CMSTATUSTYPE and differing sort directions for the row numbering.

You can get this efficiently without needing to sort in both directions using LAG and LEAD.

In the below all the rows will have 0 for their IsStartOfGroup and IsEndOfGroup values except when there is no previous or next row (respectively) in which case that flag will be set to 1.

WITH CTE AS
(
SELECT   CMCONTRACTS.CMSERIALNUMBER,
                    CMACTIVITIES.CMID,
                    CMACTIVITIES.CMSTART,
                    CMACTIVITIES.CMFINISH,
                    CMACTIVITIES.CMSTATUSTYPE,
                    LAG(0,1,1) OVER (PARTITION BY CMCONTRACTS.CMSERIALNUMBER ORDER BY CMACTIVITIES.CMFINISH  ASC) AS IsStartOfGroup,
                    LEAD(0,1,1) OVER (PARTITION BY CMCONTRACTS.CMSERIALNUMBER ORDER BY CMACTIVITIES.CMFINISH ASC) AS IsEndOfGroup
          FROM      CMACTIVITIES
          LEFT JOIN CMCONTRACTS
          ON        CMACTIVITIES.CMCONTRACTID = CMCONTRACTS.CMID
          WHERE     CMACTIVITIES.CMSTATUSTYPE IN (2,3)
)
SELECT *
FROM CTE
WHERE (CMSTATUSTYPE = 2 AND IsStartOfGroup = 1) 
    OR (CMSTATUSTYPE = 3 AND IsEndOfGroup = 1)

How to use WITH clause with UNION ALL in SQL Server

3 Answers3