User Success: full SQL Statement

Below is the full SQL that caused the “July Problem” described in the paper you have just read. As you can see, it is very long and extremely complicated and, believe it or not, it was written by a human and not a machine! The names have been changed to protect the innocent.

To SQL WorkloadExpert

SELECT
  RES.MCCD
AS
  MCCD, RES.PROCESS_DATE
AS
  PROCESSDATE, RES.PROCESS_NR
AS
  PROCESSNR, RES.LOGIS_ROUTE_NR
AS
  LOGISROUTENR, RES.AGAIN_ID
AS
  AGAINID, RES.EVAL_ART
AS
  EVALART, RES.DEP
AS
  DEPARTMENT, RES.CUST_ORDER_NR
AS
  CUSTOMERORDERNR, RES.DOC_TYPE
AS
  DOCUTYPE, RES.DOCU_SPEC
AS
  DOCUSPEC, RES.DOCU_INFO
AS
  DOCUINFO, RES.VISU_AB_TS
AS
  VISUABTS, RES.ARCHIVE_CO
AS
  ARCHIVECO, MAX (RES.WE_TS)
AS
  WETS, RES.CUST_ORDER_DATE
AS
  CUSTOMERORDERDATE
FROM (
  SELECT
    WWS09.MCCD, WWS09.PROCESS_DATE, WWS09.PROCESS_NR, LTRIM(RTRIM(
    COALESCE(CHAR(INTEGER(WWS06.LOGIS_NR)), WWS04.DELIVERER_NR, CHAR(
    WWS09.AGA_ID))))
AS
    LOGIS_ROUTE_NR, WWS09.AGA_ID, WWS09.EVAL_ART, WWS09.DEPARTMENT,
    WWS09.CUSTOMER_ORDER_NR, WWS09.DOCU_TYP, WWS09.DOCU_SPEC,
    WWS09.DOCU_INFO, WWS09.VISU_AB_TS, 'P'
AS
  ARCHIVE_CO, COALESCE(WWS10.WE_TS, WWS11.WE_TS)
AS
  WE_TS, WWS09.CUSTOMER_ORDER_DATE
FROM
  WCRKRK.WWS18_BA_ORDER_STATU WWS18
LEFT OUTER JOIN
  WCRKRK.WWS10_BA_WE_LG_ORDER WWS10
ON
  WWS10.MCCD = WWS18.MCCD
AND
  WWS10.AGA_ID = WWS18.AGA_ID
AND
  WWS10.PROCESS_DATE = WWS18.PROCESS_DATE
AND
  WWS10.PROCESS_NR = WWS18.PROCESS_NR
AND
  WWS10.EVAL_ART = WWS18.EVAL_ART
AND
  WWS10.DEPARTMENT = WWS18.DEPARTMENT
LEFT OUTER JOIN
  WCRKRK.WWS11_BA_WE_SK_BEL WWS11
ON
SELECT
  RES.MCCD


AS
  MCCD, RES.PROCESS_DATE
AS
  PROCESSDATE, RES.PROCESS_NR
AS
  PROCESSNR, RES.LOGIS_ROUTE_NR
AS
  LOGISROUTENR, RES.AGAIN_ID
AS
  AGAINID, RES.EVAL_ART
AS
  EVALART, RES.DEP
AS
  DEPARTMENT, RES.CUST_ORDER_NR
AS
  CUSTOMERORDERNR, RES.DOC_TYPE
AS
  DOCUTYPE, RES.DOCU_SPEC
AS
  DOCUSPEC, RES.DOCU_INFO
AS
  DOCUINFO, RES.VISU_AB_TS
AS
  VISUABTS, RES.ARCHIVE_CO
AS
  ARCHIVECO, MAX (RES.WE_TS)
AS
  WETS, RES.CUST_ORDER_DATE
AS
  CUSTOMERORDERDATE
FROM (
  SELECT
    WWS09.MCCD, WWS09.PROCESS_DATE, WWS09.PROCESS_NR, LTRIM(RTRIM(
    COALESCE(CHAR(INTEGER(WWS06.LOGIS_NR)), WWS04.DELIVERER_NR, CHAR(
    WWS09.AGA_ID))))
  AS
    LOGIS_ROUTE_NR, WWS09.AGA_ID, WWS09.EVAL_ART, WWS09.DEPARTMENT,
    WWS09.CUSTOMER_ORDER_NR, WWS09.DOCU_TYP, WWS09.DOCU_SPEC,
    WWS09.DOCU_INFO, WWS09.VISU_AB_TS, 'P'
  AS
    ARCHIVE_CO, COALESCE(WWS10.WE_TS, WWS11.WE_TS)
  AS
    WE_TS, WWS09.CUSTOMER_ORDER_DATE
  FROM
    WCRKRK.WWS18_BA_ORDER_STATU WWS18
  LEFT OUTER JOIN
    WCRKRK.WWS10_BA_WE_LG_ORDER WWS10
  ON
    WWS10.MCCD = WWS18.MCCD
  AND
    WWS10.AGA_ID = WWS18.AGA_ID
  AND
    WWS10.PROCESS_DATE = WWS18.PROCESS_DATE
  AND
    WWS10.PROCESS_NR = WWS18.PROCESS_NR
  AND
    WWS10.EVAL_ART = WWS18.EVAL_ART
  AND
    WWS10.DEPARTMENT = WWS18.DEPARTMENT
  LEFT OUTER JOIN
    WCRKRK.WWS11_BA_WE_SK_BEL WWS11
  ON
    WWS11.MCCD = WWS18.MCCD
  AND
    WWS11.AGA_ID = WWS18.AGA_ID
  AND
    WWS11.PROCESS_DATE = WWS18.PROCESS_DATE
  AND
    WWS11.PROCESS_NR = WWS18.PROCESS_NR
  AND
    WWS11.EVAL_ART = WWS18.EVAL_ART
 

AND
  WWS11.DEPARTMENT = WWS18.DEPARTMENT
JOIN
  WCRKRK.WWS09_BA_DOCU WWS09
ON
  WWS18.MCCD = WWS09.MCCD
AND
  WWS18.AGA_ID = WWS09.AGA_ID
AND
  WWS18.PROCESS_DATE = WWS09.PROCESS_DATE
AND
  WWS18.PROCESS_NR = WWS09.PROCESS_NR
AND
  WWS18.EVAL_ART = WWS09.EVAL_ART
AND
  WWS18.DEPARTMENT = WWS09.DEPARTMENT
LEFT OUTER JOIN
  WCRKRK.WWS06_BA_SCM_MCCD WWS06
ON
  WWS06.MCCD = WWS09.AGA_ID
LEFT OUTER JOIN
  WCRKRK.WWS04_BA_DELIVERER WWS04
ON
  WWS04.AGA_ID = WWS09.AGA_ID
LEFT OUTER JOIN
  WCRKRK.WWS18_BA_ORD_STATU WWS18A
ON
  WWS18A.MCCD = WWS18.MCCD
AND
  WWS18A.AGA_ID = WWS18.AGA_ID
AND
  WWS18A.PROCESS_DATE = WWS18.PROCESS_DATE
AND
  WWS18A.PROCESS_NR = WWS18.PROCESS_NR
AND
  WWS18A.EVAL_ART = WWS18.EVAL_ART
AND
  WWS18A.DEPARTMENT = WWS18.DEPARTMENT
AND
  WWS18A.APPCASE = 800
WHERE
  WWS18.MCCD = ?
AND ( (
  WWS18.APPCASE = 100
  AND
    WWS18.STATUS IN ( ? , ? , ? , ? , ? , ? , ? , ? , ? ) )
OR (
    WWS18.APPCASE = 500
    AND
      WWS18.STATUS IN (1)) )
AND ( (
  WWS09.VISU_AB_TS < CURRENT TIMESTAMP)
  OR (
    WWS09.DOCU_TYPE = 3
    AND
      WWS09.DOCU_SPEC = 3
    AND
      SUBSTR(WWS09.ORDER_INFO, 6, 1) = 'A'
    AND
      WWS09.VISU_AB_TS > CURRENT TIMESTAMP) )
AND
  WWS09.ORDER_TYPE <> 11
AND (
  WWS18A.STATUS IS NULL
  OR
    WWS18A.STATUS
  NOT
    IN (80, 81))
UNION
SELECT
 


  WWS09_2.MCCD, WWS09_2.PROCESS_DATE, WWS09_2.PROCESS_NR, LTRIM(RTRIM
  (COALESCE(CHAR(INTEGER(WWS06.LOGIS_NR)), WWS04.DELIVERER_NR, CHAR(
  WWS09_2.AGA_ID))))
AS
  LOGIS_ROUTE_NR, WWS09_2.AGA_ID, WWS09_2.EVAL_ART,
  WWS09_2.DEPARTMENT, WWS09_2.CUSTOMER_ORDER_NR, WWS09_2.DOCU_TYPE,
  WWS09_2.ORDER_SPEC, WWS09_2.ORDER_INFO, WWS09_2.VISU_AB_TS, 'P'
AS
 ARCHIVE_CO, COALESCE(WWS10.WE_TS, WWS11.WE_TS)
AS
 WE_TS, WWS09_2.CUSTOMER_ORDER_DATE
FROM
  WCRKRK.WWS18_BA_DOCU_STATU WWS18
JOIN
  WCRKRK.WWS09_BA_DOCU WWS09
ON
  WWS18.MCCD = WWS09.MCCD
AND
  WWS18.AGA_ID = WWS09.AGA_ID
AND
  WWS18.PROCESS_DATE = WWS09.PROCESS_DATE
AND
  WWS18.PROCESS_NR = WWS09.PROCESS_NR
AND
  WWS18.EVAL_ART = WWS09.EVAL_ART
AND
  WWS18.DEPARTMENT = WWS09.DEPARTMENT
JOIN
  WCRKRK.WWS09_BA_DOCU WWS09_2
ON
  WWS09_2.MCCD = WWS09.MCCD
AND
  WWS09_2.PROCESS_DATE = WWS09.PROCESS_DATE
AND
  WWS09_2.PROCESS_NR = WWS09.PROCESS_NR
AND
  WWS09_2.AGA_ID = WWS09.AGA_ID
AND
  WWS09_2.DEPARTMENT = WWS09.DEPARTMENT
AND
  WWS09_2.EVAL_ART <> WWS09.EVAL_ART
LEFT OUTER JOIN
  WCRKRK.WWS10_BA_WE_LG_ORD WWS10
ON
  WWS10.MCCD = WWS09_2.MCCD
AND
  WWS10.AGA_ID = WWS09_2.AGA_ID
AND
  WWS10.PROCESS_DATE = WWS09_2.PROCESS_DATE
AND
  WWS10.PROCESS_NR = WWS09_2.PROCESS_NR
AND
  WWS10.EVAL_ART = WWS09_2.EVAL_ART
AND
  WWS10.DEPARTMENT = WWS09_2.DEPARTMENT
LEFT OUTER JOIN
  WCRKRK.WWS11_BA_WE_SK_ORD WWS11
ON
  WWS11.MCCD = WWS09_2.MCCD
AND
  WWS11.AGA_ID = WWS09_2.AGA_ID
AND
  WWS11.PROCESS_DATE = WWS09_2.PROCESS_DATE
AND
  WWS11.PROCESS_NR = WWS09_2.PROCESS_NR
AND
  WWS11.EVAL_ART = WWS09_2.EVAL_ART
AND
  WWS11.DEPARTMENT = WWS09_2.DEPARTMENT
LEFT OUTER JOIN
 

  WCRKRK.WWS06_BA_SCM_MCCD WWS06
ON
  WWS06.MCCD = WWS09_2.AGA_ID
LEFT OUTER JOIN
  WCRKRK.WWS04_BA_DELIVERER WWS04
ON
  WWS04.AGA_ID = WWS09_2.AGA_ID
LEFT OUTER JOIN
  WCRKRK.WWS18_BA_ORD_STATU WWS18A
ON
  WWS18A.MCCD = WWS18.MCCD

  WWS18A.AGA_ID = WWS18.AGA_ID
AND
  WWS18A.PROCESS_DATE = WWS18.PROCESS_DATE
AND
  WWS18A.PROCESS_NR = WWS18.PROCESS_NR
AND
  WWS18A.EVAL_ART = WWS18.EVAL_ART
AND
  WWS18A.DEPARTMENT = WWS18.DEPARTMENT
AND
  WWS18A.APPCASE = 800
WHERE
  WWS18.MCCD= ?
AND ( (
   WWS18.APPCASE = 100
   AND
     WWS18.STATUS IN ( ? , ? , ? , ? , ? , ? , ? , ? , ? ) )
   OR (
     WWS18.APPCASE = 500
     AND
       WWS18.STATUS IN (1)) )
AND ( (
  WWS09.VISU_AB_TS < CURRENT TIMESTAMP)
  OR (
    WWS09.DOCU_TYPE = 3
    AND
      WWS09.DOCU_SPEC = 3
    AND
      SUBSTR(WWS09.DOCU_INFO, 6, 1) = 'A'
    AND
      WWS09.VISU_AB_TS > CURRENT TIMESTAMP) )
AND
  WWS09.DOCU_TYPE <> 11
AND (
  WWS18A.STATUS IS NULL
  OR
    WWS18A.STATUS
  NOT
    IN (80, 81)) )

AS
  ERG
GROUP BY
  RES.MCCD, RES.PROCESS_DATE, RES.PROCESS_NR, RES.LOGIS_ROUTE_NR,
  RES.AGA_ID, RES.EVAL_ART, RES.DEPARTMENT, RES.CUSTOMER_ORDER_NR,
  RES.DOCU_TYPE, RES.DOCU_SPEC, RES.DOCU_INFO, RES.VISU_AB_TS,
  RES.ARCHIVE_CO, RES.CUSTOMER_ORDER_DATE
WITH UR