Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

How to UNION data set of multiple WITH Clause queries

antobayJan 16 2017 — edited Jan 17 2017

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.

ORA-00933: SQL command not properly ended

00933. 00000 -  "SQL command not properly ended"

*Cause:   

*Action:

Error at Line: 1 Column: 7,103

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.

P

This post has been answered by Ahmed Haroon on Jan 17 2017
Jump to Answer
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Feb 14 2017
Added on Jan 16 2017
12 comments
1,204 views