Hello expert, Please I need your help to union results of two WITH Clause queries. The original query was a select from a union of two queries but the performance was terrible, therefore I decided to use a With Clause and the performance was good. But I stated getting syntax error below when I tried to UNION the two with Clause queries.
00933. 00000 - "SQL command not properly ended"
WITH
QRY1 AS
(SELECT
TO_CHAR(SYSDATE,'DD/MM/YYYY HH24:MI:SS') DATE_LAST_UPDATED,
SXS_GAU.Y_DTT_HISTORY.PAP_ERA,
SXS_GAU.Y_DTT_HISTORY.ACCOUNT_NUMBER,
SXS_GAU.Y_DTT_HISTORY.PAC_FSAH,
SXS_GAU.Y_DTT_HISTORY.PAC_STEP,
SXS_GAU.Y_DTT_HISTORY.WPE_JOB,
SRS_PAC.PAC_PCCT,
SXS_GAU.Y_DTT_HISTORY.DTD_COURSE,
PASS_FAIL_DATE.MAX_PASS_FAIL_DATETIME
FROM SXS_GAU.Y_DTT_HISTORY
JOIN SRS_PAC
ON SXS_GAU.Y_DTT_HISTORY.PAP_ERA=SRS_PAC.PAC_NOTY
AND SXS_GAU.Y_DTT_HISTORY.ACCOUNT_NUMBER=SRS_PAC.PAC_NO
AND SXS_GAU.Y_DTT_HISTORY.PAC_FSAH=SRS_PAC.PAC_FSAH
AND SXS_GAU.Y_DTT_HISTORY.PAC_STEP=SRS_PAC.PAC_STEP
JOIN (SELECT PAP_ERA, ACCOUNT_NUMBER, PAC_FSAH, PAC_STEP, WPE_JOB, MAX(CREATED_TIMESTAMP) AS MAX_PASS_FAIL_DATETIME
FROM SXS_GAU.Y_DTT_HISTORY WHERE DTD_COURSE IN ('PASS', 'FAIL') GROUP BY PAP_ERA, ACCOUNT_NUMBER, PAC_FSAH, PAC_STEP, WPE_JOB) PASS_FAIL_DATE
ON SXS_GAU.Y_DTT_HISTORY.PAP_ERA=PASS_FAIL_DATE.PAP_ERA
AND SXS_GAU.Y_DTT_HISTORY.ACCOUNT_NUMBER=PASS_FAIL_DATE.ACCOUNT_NUMBER
AND SXS_GAU.Y_DTT_HISTORY.PAC_FSAH=PASS_FAIL_DATE.PAC_FSAH
AND SXS_GAU.Y_DTT_HISTORY.PAC_STEP=PASS_FAIL_DATE.PAC_STEP
AND SXS_GAU.Y_DTT_HISTORY.WPE_JOB=PASS_FAIL_DATE.WPE_JOB
AND SXS_GAU.Y_DTT_HISTORY.CREATED_TIMESTAMP=PASS_FAIL_DATE.MAX_PASS_FAIL_DATETIME
WHERE SXS_GAU.Y_DTT_HISTORY.PAP_ERA IN ('2013', '2014', '2015','2016')
AND SXS_GAU.Y_DTT_HISTORY.DTD_COURSE <> 'PS OVERRIDDEN'
AND PAC_AESC = 'U'
AND PAC_CLYN = 'N'
AND PAC_STAC NOT LIKE 'Z%'
AND PAC_BLOK IN ('1','0')
AND PAC_YEAR IN ('16/17')
AND PAC_NOTY <> '2017'
AND CASE WHEN SUBSTR(PAC_YEAR,1,2) = SUBSTR(PAC_NOTY,3,2) THEN 'N' ELSE 'Y' END = 'N' ),
QRY2 AS
( SELECT
TO_CHAR(SYSDATE,'DD/MM/YYYY HH24:MI:SS') DATE_LAST_UPDATED,
SXS_GAU.Y_DTT_HISTORY.PAP_ERA,
SXS_GAU.Y_DTT_HISTORY.ACCOUNT_NUMBER,
SXS_GAU.Y_DTT_HISTORY.PAC_FSAH,
SXS_GAU.Y_DTT_HISTORY.PAC_STEP,
SXS_GAU.Y_DTT_HISTORY.WPE_JOB,
CAST(RST.RST_MAS_PAC_HISTORY.PAC_PCCT AS NVARCHAR2(4)) AS PAC_PCCT,
SXS_GAU.Y_DTT_HISTORY.DTD_COURSE,
PASS_FAIL_DATE.MAX_PASS_FAIL_DATETIME,
FROM SXS_GAU.Y_DTT_HISTORY
JOIN RST.RST_MAS_PAC_HISTORY@PROD
ON SXS_GAU.Y_DTT_HISTORY.PAP_ERA=RST.RST_MAS_PAC_HISTORY.PAC_NOTY
AND SXS_GAU.Y_DTT_HISTORY.ACCOUNT_NUMBER=RST.RST_MAS_PAC_HISTORY.PAC_NO
AND SXS_GAU.Y_DTT_HISTORY.PAC_FSAH=RST.RST_MAS_PAC_HISTORY.PAC_FSAH
AND SXS_GAU.Y_DTT_HISTORY.PAC_STEP=RST.RST_MAS_PAC_HISTORY.PAC_STEP
JOIN (SELECT PAP_ERA, ACCOUNT_NUMBER, PAC_FSAH, PAC_STEP, WPE_JOB, MAX(CREATED_TIMESTAMP) AS MAX_PASS_FAIL_DATETIME
FROM SXS_GAU.Y_DTT_HISTORY WHERE DTD_COURSE IN ('PASS', 'FAIL') GROUP BY PAP_ERA, ACCOUNT_NUMBER, PAC_FSAH, PAC_STEP, WPE_JOB) PASS_FAIL_DATE
ON SXS_GAU.Y_DTT_HISTORY.PAP_ERA=PASS_FAIL_DATE.PAP_ERA
AND SXS_GAU.Y_DTT_HISTORY.ACCOUNT_NUMBER=PASS_FAIL_DATE.ACCOUNT_NUMBER
AND SXS_GAU.Y_DTT_HISTORY.PAC_FSAH=PASS_FAIL_DATE.PAC_FSAH
AND SXS_GAU.Y_DTT_HISTORY.PAC_STEP=PASS_FAIL_DATE.PAC_STEP
AND SXS_GAU.Y_DTT_HISTORY.WPE_JOB=PASS_FAIL_DATE.WPE_JOB
AND SXS_GAU.Y_DTT_HISTORY.CREATED_TIMESTAMP=PASS_FAIL_DATE.MAX_PASS_FAIL_DATETIME
WHERE SXS_GAU.Y_DTT_HISTORY.PAP_ERA IN ('2013', '2014', '2015','2016')
AND SXS_GAU.Y_DTT_HISTORY.DTD_COURSE <> 'PS OVERRIDDEN'
AND PAC_AESC = 'U'
AND PAC_CLYN = 'N'
AND PAC_STAC NOT LIKE 'Z%'
AND PAC_BLOK IN ('1','0')
AND PAC_YEAR IN ('13/14','14/15','15/16')
AND CASE WHEN SUBSTR(PAC_YEAR,1,2) = SUBSTR(PAC_NOTY,3,2) THEN 'N' ELSE 'Y' END = 'N')
SELECT PAP_ERA,
PAC_PCCT,
WPE_JOB,
SUM(DECODE(DTD_COURSE,'FAIL',SCORED_TO_DATE,0)) AS "FAIL_TO_DATE",
SUM(DECODE(DTD_COURSE,'PASS',SCORED_TO_DATE,0)) AS "PASS_TO_DATE",
SUM(DECODE(DTD_COURSE,'FAIL',SCORED_FUTURE,0)) AS "FAIL_FUTURE",
SUM(DECODE(DTD_COURSE,'PASS',SCORED_FUTURE,0)) AS "PASS_FUTURE",
SUM(SCORED_TO_DATE) AS SCORED_TO_DATE
FROM QRY1
UNION
SELECT PAP_ERA,PAC_PCCT,
WPE_JOB,
SUM(DECODE(DTD_COURSE,'FAIL',SCORED_TO_DATE,0)) AS "FAIL_TO_DATE",
SUM(DECODE(DTD_COURSE,'PASS',SCORED_TO_DATE,0)) AS "PASS_TO_DATE",
SUM(DECODE(DTD_COURSE,'FAIL',SCORED_FUTURE,0)) AS "FAIL_FUTURE",
SUM(DECODE(DTD_COURSE,'PASS',SCORED_FUTURE,0)) AS "PASS_FUTURE",
SUM(SCORED_TO_DATE) AS SCORED_TO_DATE
FROM QRY2
GROUP BY PAP_ERA, PAC_PCCT, WPE_JOB
ORDER BY PAP_ERA, PAC_PCCT, WPE_JOB;
Many thanks for your help.