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.
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