Seek DB2 translation of complicated Oracle join statment

[gimme_this_gimme_that]

Can this statement be translated into DB2 ?
CREATE VIEW RELEASE_REPORTS AS (SELECT RI.GROUP_ID AS GROUP_ID,
RI.RELEASE_ID AS RELEASE_ID,
RI.TARGET_REL_I D, RI.REL_DROP, B.DROPCOUNT ,
DECODE(RI.REL_D ROP, NULL , B.DROPCOUNT ) AS RCOUNT,
RI.CREATED_DATE AS DATE_ENTERED,
RI.QA_TARGET_DA TE AS DATE_SCH_QA,
RI.TARGET_DATE AS DATE_SCH_PRD, Y.READY_FOR_QA,
X.DATA_VAL ENV, NVL(Z.REJECTS, 0) AS REJECT,
X.CREATED_DATE AS CREATED_DATE, X.REQ_DATE, X.REQ_START_TIM E,
X.EST_COMP_DATE , X.EST_COMP_TIME , X.MIN_MODIFIED_ DATE,
D.DATA_VAL AS STATUS,
X.SCM_DATE SCM_DATE,
CHAR(QUARTER(DE PSTAT.MODIFIED_ DATE)) Q,
CHAR(MONTH(DEPS TAT.MODIFIED_DA TE)) MON,
CHAR(DATE((DEPS TAT.MODIFIED_DA TE + (8 -
DAYOFWEEK(DEPST AT.MODIFIED_DAT E)) DAYS)),USA) WEEKEND,
CHAR(YEAR(DEPST AT.MODIFIED_DAT E)) YR
FROM RM_RELEASE_INFO RI, (
SELECT ENV.RELEASE_ID, ENV.DEPLOY_REQ_ ID,
DATA.DATA_VAL, ENV.CREATED_DAT E, ENV.REQ_DATE,
ENV.REQ_START_T IME, ENV.EST_COMP_DA TE,
ENV.EST_COMP_TI ME, A.SCM_DATE, A.MIN_MODIFIED_ DATE
FROM RM_LABELS_DATA DATA, RM_LABELS LABEL,
RM_DEPLOY_REQ_I NFO ENV,
(SELECT R2.DEPLOY_REQ_I D,
R1.SCM_DATE,
R2.MIN_MODIFIED _DATE
FROM (
SELECT DEPLOY_REQ_ID, MAX(MODIFIED_DA TE) SCM_DATE
FROM RM_DEPLOY_REQ_S TATUS
WHERE DEPLOY_STATUS = 8
GROUP BY DEPLOY_REQ_ID
) R1 LEFT OUTER JOIN (
SELECT DEPLOY_REQ_ID,
MIN(MODIFIED_DA TE) MIN_MODIFIED_DA TE
FROM RM_DEPLOY_REQ_S TATUS
GROUP BY DEPLOY_REQ_ID
) R2
ON R1.DEPLOY_REQ_I D = R2.DEPLOY_REQ_I D
) A
WHERE LABEL.LABEL_ID = DATA.LABEL_LABE L_ID
AND LABEL_ID = 8
AND DATA.DATA_ID = ENV.TARGET_ENV
AND ENV.DEPLOY_REQ_ ID = A.DEPLOY_REQ_ID

) X, (
SELECT RELEASE_ID, MAX(SENT_DATE) AS READY_FOR_QA
FROM RM_EMAIL_HISTOR Y
WHERE EMAIL_TYPE = 1
GROUP BY RELEASE_ID
) Y, (
SELECT RELEASE_ID, COUNT(*) AS REJECTS
FROM RM_EMAIL_HISTOR Y
WHERE EMAIL_TYPE = 3
GROUP BY RELEASE_ID
) Z, (
SELECT TARGET_REL_ID, COUNT(*) AS DROPCOUNT
FROM RM_RELEASE_INFO
WHERE REL_DROP IS NOT NULL
GROUP BY TARGET_REL_ID
) B,
(
SELECT INFO.RELEASE_ID , DATA.DATA_VAL
FROM RM_RELEASE_INFO INFO, RM_LABELS_DATA DATA
WHERE INFO.STATE = DATA.LABEL_LABE L_ID
AND INFO.STATUS = DATA.DATA_ID
) D
WHERE RI.RELEASE_ID = X.RELEASE_ID
AND RI.RELEASE_ID = Y.RELEASE_ID(+)
AND RI.RELEASE_ID = Z.RELEASE_ID(+)
AND RI.TARGET_REL_I D = B.TARGET_REL_ID (+)
AND RI.RELEASE_ID = D.RELEASE_ID
)

[Serge Rielau]

gi************* ******@yahoo.co m wrote:

Can this statement be translated into DB2 ?
CREATE VIEW RELEASE_REPORTS AS (SELECT RI.GROUP_ID AS GROUP_ID,
RI.RELEASE_ID AS RELEASE_ID,
RI.TARGET_REL_I D, RI.REL_DROP, B.DROPCOUNT ,
DECODE(RI.REL_D ROP, NULL , B.DROPCOUNT ) AS RCOUNT,
RI.CREATED_DATE AS DATE_ENTERED,
RI.QA_TARGET_DA TE AS DATE_SCH_QA,
RI.TARGET_DATE AS DATE_SCH_PRD, Y.READY_FOR_QA,
X.DATA_VAL ENV, NVL(Z.REJECTS, 0) AS REJECT,
X.CREATED_DATE AS CREATED_DATE, X.REQ_DATE, X.REQ_START_TIM E,
X.EST_COMP_DATE , X.EST_COMP_TIME , X.MIN_MODIFIED_ DATE,
D.DATA_VAL AS STATUS,
X.SCM_DATE SCM_DATE,
CHAR(QUARTER(DE PSTAT.MODIFIED_ DATE)) Q,
CHAR(MONTH(DEPS TAT.MODIFIED_DA TE)) MON,
CHAR(DATE((DEPS TAT.MODIFIED_DA TE + (8 -
DAYOFWEEK(DEPST AT.MODIFIED_DAT E)) DAYS)),USA) WEEKEND,
CHAR(YEAR(DEPST AT.MODIFIED_DAT E)) YR
FROM RM_RELEASE_INFO RI, (
SELECT ENV.RELEASE_ID, ENV.DEPLOY_REQ_ ID,
DATA.DATA_VAL, ENV.CREATED_DAT E, ENV.REQ_DATE,
ENV.REQ_START_T IME, ENV.EST_COMP_DA TE,
ENV.EST_COMP_TI ME, A.SCM_DATE, A.MIN_MODIFIED_ DATE
FROM RM_LABELS_DATA DATA, RM_LABELS LABEL,
RM_DEPLOY_REQ_I NFO ENV,
(SELECT R2.DEPLOY_REQ_I D,
R1.SCM_DATE,
R2.MIN_MODIFIED _DATE
FROM (
SELECT DEPLOY_REQ_ID, MAX(MODIFIED_DA TE) SCM_DATE
FROM RM_DEPLOY_REQ_S TATUS
WHERE DEPLOY_STATUS = 8
GROUP BY DEPLOY_REQ_ID
) R1 LEFT OUTER JOIN (
SELECT DEPLOY_REQ_ID,
MIN(MODIFIED_DA TE) MIN_MODIFIED_DA TE
FROM RM_DEPLOY_REQ_S TATUS
GROUP BY DEPLOY_REQ_ID
) R2
ON R1.DEPLOY_REQ_I D = R2.DEPLOY_REQ_I D
) A
WHERE LABEL.LABEL_ID = DATA.LABEL_LABE L_ID
AND LABEL_ID = 8
AND DATA.DATA_ID = ENV.TARGET_ENV
AND ENV.DEPLOY_REQ_ ID = A.DEPLOY_REQ_ID

) X, (
SELECT RELEASE_ID, MAX(SENT_DATE) AS READY_FOR_QA
FROM RM_EMAIL_HISTOR Y
WHERE EMAIL_TYPE = 1
GROUP BY RELEASE_ID
) Y, (
SELECT RELEASE_ID, COUNT(*) AS REJECTS
FROM RM_EMAIL_HISTOR Y
WHERE EMAIL_TYPE = 3
GROUP BY RELEASE_ID
) Z, (
SELECT TARGET_REL_ID, COUNT(*) AS DROPCOUNT
FROM RM_RELEASE_INFO
WHERE REL_DROP IS NOT NULL
GROUP BY TARGET_REL_ID
) B,
(
SELECT INFO.RELEASE_ID , DATA.DATA_VAL
FROM RM_RELEASE_INFO INFO, RM_LABELS_DATA DATA
WHERE INFO.STATE = DATA.LABEL_LABE L_ID
AND INFO.STATUS = DATA.DATA_ID
) D
WHERE RI.RELEASE_ID = X.RELEASE_ID
AND RI.RELEASE_ID = Y.RELEASE_ID(+)
AND RI.RELEASE_ID = Z.RELEASE_ID(+)
AND RI.TARGET_REL_I D = B.TARGET_REL_ID (+)
AND RI.RELEASE_ID = D.RELEASE_ID
)

I think this should do (I assume (+) is on the NULL producing side)

CREATE VIEW RELEASE_REPORTS AS (SELECT RI.GROUP_ID AS GROUP_ID,
RI.RELEASE_ID AS RELEASE_ID,
RI.TARGET_REL_I D, RI.REL_DROP, B.DROPCOUNT ,
DECODE(RI.REL_D ROP, NULL , B.DROPCOUNT ) AS RCOUNT,
RI.CREATED_DATE AS DATE_ENTERED,
RI.QA_TARGET_DA TE AS DATE_SCH_QA,
RI.TARGET_DATE AS DATE_SCH_PRD, Y.READY_FOR_QA,
X.DATA_VAL ENV, NVL(Z.REJECTS, 0) AS REJECT,
X.CREATED_DATE AS CREATED_DATE, X.REQ_DATE, X.REQ_START_TIM E,
X.EST_COMP_DATE , X.EST_COMP_TIME , X.MIN_MODIFIED_ DATE,
D.DATA_VAL AS STATUS,
X.SCM_DATE SCM_DATE,
CHAR(QUARTER(DE PSTAT.MODIFIED_ DATE)) Q,
CHAR(MONTH(DEPS TAT.MODIFIED_DA TE)) MON,
CHAR(DATE((DEPS TAT.MODIFIED_DA TE + (8 -
DAYOFWEEK(DEPST AT.MODIFIED_DAT E)) DAYS)),USA) WEEKEND,
CHAR(YEAR(DEPST AT.MODIFIED_DAT E)) YR
FROM RM_RELEASE_INFO RI
INNER JOIN
(SELECT ENV.RELEASE_ID, ENV.DEPLOY_REQ_ ID,
DATA.DATA_VAL, ENV.CREATED_DAT E, ENV.REQ_DATE,
ENV.REQ_START_T IME, ENV.EST_COMP_DA TE,
ENV.EST_COMP_TI ME, A.SCM_DATE, A.MIN_MODIFIED_ DATE
FROM RM_LABELS_DATA DATA, RM_LABELS LABEL,
RM_DEPLOY_REQ_I NFO ENV,
(SELECT R2.DEPLOY_REQ_I D,
R1.SCM_DATE,
R2.MIN_MODIFIED _DATE
FROM (
SELECT DEPLOY_REQ_ID, MAX(MODIFIED_DA TE) SCM_DATE
FROM RM_DEPLOY_REQ_S TATUS
WHERE DEPLOY_STATUS = 8
GROUP BY DEPLOY_REQ_ID
) R1 LEFT OUTER JOIN (
SELECT DEPLOY_REQ_ID,
MIN(MODIFIED_DA TE) MIN_MODIFIED_DA TE
FROM RM_DEPLOY_REQ_S TATUS
GROUP BY DEPLOY_REQ_ID
) R2
ON R1.DEPLOY_REQ_I D = R2.DEPLOY_REQ_I D
) A
WHERE LABEL.LABEL_ID = DATA.LABEL_LABE L_ID
AND LABEL_ID = 8
AND DATA.DATA_ID = ENV.TARGET_ENV
AND ENV.DEPLOY_REQ_ ID = A.DEPLOY_REQ_ID

) X
ON RI.RELEASE_ID = X.RELEASE_ID
LEFT OUTER JOIN (
SELECT RELEASE_ID, MAX(SENT_DATE) AS READY_FOR_QA
FROM RM_EMAIL_HISTOR Y
WHERE EMAIL_TYPE = 1
GROUP BY RELEASE_ID
) Y
ON RI.RELEASE_ID = Y.RELEASE_ID
LEFT OUTER JOIN (
SELECT RELEASE_ID, COUNT(*) AS REJECTS
FROM RM_EMAIL_HISTOR Y
WHERE EMAIL_TYPE = 3
GROUP BY RELEASE_ID
) Z
ON RI.RELEASE_ID = Z.RELEASE_ID
LEFT OUTER JOIN (
SELECT TARGET_REL_ID, COUNT(*) AS DROPCOUNT
FROM RM_RELEASE_INFO
WHERE REL_DROP IS NOT NULL
GROUP BY TARGET_REL_ID
) B
ON RI.TARGET_REL_I D = B.TARGET_REL_ID
INNER JOIN
(
SELECT INFO.RELEASE_ID , DATA.DATA_VAL
FROM RM_RELEASE_INFO INFO, RM_LABELS_DATA DATA
WHERE INFO.STATE = DATA.LABEL_LABE L_ID
AND INFO.STATUS = DATA.DATA_ID
) D
ON RI.RELEASE_ID = D.RELEASE_ID
)

--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab

[gimme_this_gimme_that]

Thanks Serge,

I got it and without your help I'd still be stuck.

Thanks so much.