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 take in one query, results from both previous SQL queries including the WITH
clause.
CodePudding user response:
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;
CodePudding user response:
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.