By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
437,586 Members | 2,418 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 437,586 IT Pros & Developers. It's quick & easy.

Seek DB2 translation of complicated Oracle join statment

P: n/a
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_ID, RI.REL_DROP, B.DROPCOUNT ,
DECODE(RI.REL_DROP, NULL , B.DROPCOUNT ) AS RCOUNT,
RI.CREATED_DATE AS DATE_ENTERED,
RI.QA_TARGET_DATE 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_TIME,
X.EST_COMP_DATE, X.EST_COMP_TIME, X.MIN_MODIFIED_DATE,
D.DATA_VAL AS STATUS,
X.SCM_DATE SCM_DATE,
CHAR(QUARTER(DEPSTAT.MODIFIED_DATE)) Q,
CHAR(MONTH(DEPSTAT.MODIFIED_DATE)) MON,
CHAR(DATE((DEPSTAT.MODIFIED_DATE + (8 -
DAYOFWEEK(DEPSTAT.MODIFIED_DATE)) DAYS)),USA) WEEKEND,
CHAR(YEAR(DEPSTAT.MODIFIED_DATE)) YR
FROM RM_RELEASE_INFO RI, (
SELECT ENV.RELEASE_ID, ENV.DEPLOY_REQ_ID,
DATA.DATA_VAL, ENV.CREATED_DATE, ENV.REQ_DATE,
ENV.REQ_START_TIME, ENV.EST_COMP_DATE,
ENV.EST_COMP_TIME, A.SCM_DATE, A.MIN_MODIFIED_DATE
FROM RM_LABELS_DATA DATA, RM_LABELS LABEL,
RM_DEPLOY_REQ_INFO ENV,
(SELECT R2.DEPLOY_REQ_ID,
R1.SCM_DATE,
R2.MIN_MODIFIED_DATE
FROM (
SELECT DEPLOY_REQ_ID, MAX(MODIFIED_DATE) SCM_DATE
FROM RM_DEPLOY_REQ_STATUS
WHERE DEPLOY_STATUS = 8
GROUP BY DEPLOY_REQ_ID
) R1 LEFT OUTER JOIN (
SELECT DEPLOY_REQ_ID,
MIN(MODIFIED_DATE) MIN_MODIFIED_DATE
FROM RM_DEPLOY_REQ_STATUS
GROUP BY DEPLOY_REQ_ID
) R2
ON R1.DEPLOY_REQ_ID = R2.DEPLOY_REQ_ID
) A
WHERE LABEL.LABEL_ID = DATA.LABEL_LABEL_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_HISTORY
WHERE EMAIL_TYPE = 1
GROUP BY RELEASE_ID
) Y, (
SELECT RELEASE_ID, COUNT(*) AS REJECTS
FROM RM_EMAIL_HISTORY
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_LABEL_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_ID = B.TARGET_REL_ID(+)
AND RI.RELEASE_ID = D.RELEASE_ID
)

Nov 12 '05 #1
Share this Question
Share on Google+
2 Replies


P: n/a
gi*******************@yahoo.com 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_ID, RI.REL_DROP, B.DROPCOUNT ,
DECODE(RI.REL_DROP, NULL , B.DROPCOUNT ) AS RCOUNT,
RI.CREATED_DATE AS DATE_ENTERED,
RI.QA_TARGET_DATE 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_TIME,
X.EST_COMP_DATE, X.EST_COMP_TIME, X.MIN_MODIFIED_DATE,
D.DATA_VAL AS STATUS,
X.SCM_DATE SCM_DATE,
CHAR(QUARTER(DEPSTAT.MODIFIED_DATE)) Q,
CHAR(MONTH(DEPSTAT.MODIFIED_DATE)) MON,
CHAR(DATE((DEPSTAT.MODIFIED_DATE + (8 -
DAYOFWEEK(DEPSTAT.MODIFIED_DATE)) DAYS)),USA) WEEKEND,
CHAR(YEAR(DEPSTAT.MODIFIED_DATE)) YR
FROM RM_RELEASE_INFO RI, (
SELECT ENV.RELEASE_ID, ENV.DEPLOY_REQ_ID,
DATA.DATA_VAL, ENV.CREATED_DATE, ENV.REQ_DATE,
ENV.REQ_START_TIME, ENV.EST_COMP_DATE,
ENV.EST_COMP_TIME, A.SCM_DATE, A.MIN_MODIFIED_DATE
FROM RM_LABELS_DATA DATA, RM_LABELS LABEL,
RM_DEPLOY_REQ_INFO ENV,
(SELECT R2.DEPLOY_REQ_ID,
R1.SCM_DATE,
R2.MIN_MODIFIED_DATE
FROM (
SELECT DEPLOY_REQ_ID, MAX(MODIFIED_DATE) SCM_DATE
FROM RM_DEPLOY_REQ_STATUS
WHERE DEPLOY_STATUS = 8
GROUP BY DEPLOY_REQ_ID
) R1 LEFT OUTER JOIN (
SELECT DEPLOY_REQ_ID,
MIN(MODIFIED_DATE) MIN_MODIFIED_DATE
FROM RM_DEPLOY_REQ_STATUS
GROUP BY DEPLOY_REQ_ID
) R2
ON R1.DEPLOY_REQ_ID = R2.DEPLOY_REQ_ID
) A
WHERE LABEL.LABEL_ID = DATA.LABEL_LABEL_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_HISTORY
WHERE EMAIL_TYPE = 1
GROUP BY RELEASE_ID
) Y, (
SELECT RELEASE_ID, COUNT(*) AS REJECTS
FROM RM_EMAIL_HISTORY
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_LABEL_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_ID = 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_ID, RI.REL_DROP, B.DROPCOUNT ,
DECODE(RI.REL_DROP, NULL , B.DROPCOUNT ) AS RCOUNT,
RI.CREATED_DATE AS DATE_ENTERED,
RI.QA_TARGET_DATE 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_TIME,
X.EST_COMP_DATE, X.EST_COMP_TIME, X.MIN_MODIFIED_DATE,
D.DATA_VAL AS STATUS,
X.SCM_DATE SCM_DATE,
CHAR(QUARTER(DEPSTAT.MODIFIED_DATE)) Q,
CHAR(MONTH(DEPSTAT.MODIFIED_DATE)) MON,
CHAR(DATE((DEPSTAT.MODIFIED_DATE + (8 -
DAYOFWEEK(DEPSTAT.MODIFIED_DATE)) DAYS)),USA) WEEKEND,
CHAR(YEAR(DEPSTAT.MODIFIED_DATE)) YR
FROM RM_RELEASE_INFO RI
INNER JOIN
(SELECT ENV.RELEASE_ID, ENV.DEPLOY_REQ_ID,
DATA.DATA_VAL, ENV.CREATED_DATE, ENV.REQ_DATE,
ENV.REQ_START_TIME, ENV.EST_COMP_DATE,
ENV.EST_COMP_TIME, A.SCM_DATE, A.MIN_MODIFIED_DATE
FROM RM_LABELS_DATA DATA, RM_LABELS LABEL,
RM_DEPLOY_REQ_INFO ENV,
(SELECT R2.DEPLOY_REQ_ID,
R1.SCM_DATE,
R2.MIN_MODIFIED_DATE
FROM (
SELECT DEPLOY_REQ_ID, MAX(MODIFIED_DATE) SCM_DATE
FROM RM_DEPLOY_REQ_STATUS
WHERE DEPLOY_STATUS = 8
GROUP BY DEPLOY_REQ_ID
) R1 LEFT OUTER JOIN (
SELECT DEPLOY_REQ_ID,
MIN(MODIFIED_DATE) MIN_MODIFIED_DATE
FROM RM_DEPLOY_REQ_STATUS
GROUP BY DEPLOY_REQ_ID
) R2
ON R1.DEPLOY_REQ_ID = R2.DEPLOY_REQ_ID
) A
WHERE LABEL.LABEL_ID = DATA.LABEL_LABEL_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_HISTORY
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_HISTORY
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_ID = 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_LABEL_ID
AND INFO.STATUS = DATA.DATA_ID
) D
ON RI.RELEASE_ID = D.RELEASE_ID
)

--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab
Nov 12 '05 #2

P: n/a
Thanks Serge,

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

Thanks so much.

Nov 12 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.