May 19, 2011 at 2:29 pm
told ya i was new at this 🙂
May 19, 2011 at 2:39 pm
Well I can't help anymore at this point. Good luck!
May 19, 2011 at 11:30 pm
Not being nasty, but you may want to apologise to MM as this likely wasted quite a few hours of his time.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 20, 2011 at 1:58 am
Oracle (which version, BTW?) does not support CROSS APPLY. You can chenge it to a CROSS JOIN:
SELECT A.REPORT_DATE,
A.REPORT_TYPE,
A.ORD_NUM,
A.CKT_QTY,
A.SUPP_VER,
A.ORD_TYPE,
A.ORDERPRO_TIN,
A.BILLABLE,
A.VENDOR,
A.E_MRV_FLAG,
A.MRV,
A.ORDER_EXPEDITE,
A.ORDER_EXPEDITE_TYPE,
A.PROJECT,
A.NASPID,
A.CONV_NASPID,
A.CUST_NAME,
'' AS RYG,
REGION,
A.DOMESTIC_INTL,
A.CPE_FLAG,
A.MNGD_FLAG,
A.SYSTEM,
A.PRODUCT,
(CASE WHEN A.CKT_TYPE LIKE 'EPL%' THEN 'EPL'
WHEN A.CKT_TYPE LIKE '10G%' THEN 'EPL'
ELSE ''
END) AS SUB_CATEGORY,
A.SPEED,
A.SPEED_CAT,
(CASE WHEN A.PROV_SUPERVISOR = 'JONES, BOB' THEN 'JONES'
WHEN A.PROV_SUPERVISOR = 'SMITH, JOHN' THEN 'SMITH'
WHEN (A.PRODUCT = 'IDE'
OR A.PRODUCT = 'INET'
)
AND A.SYSTEM = 'OTS'
AND A.SPEED <> 'DS0'
AND A.SPEED <> 'DS1' THEN 'WILLIAMS'
WHEN A.PROV_SUPERVISOR = 'JAMES, LEBRON' THEN 'JAMES'
WHEN A.PROV_SUPERVISOR = 'WALTERS, BARBARA' THEN 'JONES'
ELSE ''
END) AS TEAM,
A.PROV_SUPERVISOR,
A.PRODUCT_FAMILY,
A.NEW_TELCO,
A.STATUS,
A.PEND_RSN,
(CASE WHEN A.PEND_RSN = 'CNCC' THEN 'FIRM/CSCH/INSTALL'
WHEN A.PEND_RSN = 'FIRM' THEN 'FIRM/CSCH/INSTALL'
WHEN A.PEND_RSN = 'CSCH' THEN 'FIRM/CSCH/INSTALL'
WHEN A.POM_STATUS = 'NATIONAL LOCAL INSTALL '
THEN 'FIRM/CSCH/INSTALL'
WHEN A.PEND_RSN = 'SRMS' THEN 'FIRM/CSCH/INSTALL'
WHEN A.PEND_RSN = 'CCPE' THEN 'FIRM/CSCH/INSTALL'
WHEN A.PEND_RSN = 'CORD' THEN 'FIRM/CSCH/INSTALL'
WHEN A.POM_STATUS = 'Ntwk Eng [&] Install'
THEN 'FIRM/CSCH/INSTALL'
WHEN A.POM_STATUS = 'Test [&] Install' THEN 'FIRM/CSCH/INSTALL'
ELSE 'OTHER'
END) AS PEND_CATEGORY,
A.POM_STATUS,
A.POM_SUBSTATUS,
A.ACTIVE_FLAG,
A.BASELINE_DESC,
A.DATE_STRT,
A.DATE_CRDD,
(CASE WHEN ENTERPRISE.UTIL.NETWORKDAYS(A.REPORT_DATE, A.DATE_CRDD) - 1 > 15
THEN '16+'
WHEN ENTERPRISE.UTIL.NETWORKDAYS(A.REPORT_DATE, A.DATE_CRDD) - 1 > 10
THEN '11-15'
WHEN ENTERPRISE.UTIL.NETWORKDAYS(A.REPORT_DATE, A.DATE_CRDD) - 1 > 5
THEN '6-10'
WHEN ENTERPRISE.UTIL.NETWORKDAYS(A.REPORT_DATE, A.DATE_CRDD) - 1 > 0
THEN '0-5'
ELSE 'PAST DUE'
END) AS BUCKET,
A.DATE_CMIT,
A.DATE_ICOM,
A.DATE_BCOM,
A.ORD_BD,
A.CRDD_BD,
A.TARGET,
A.NEW_PPD_FLAG,
A.MISS_MET_COMMIT,
A.MISS_MET_CRDD,
A.MISS_MET_TARGET,
A.CENTER,
A.JR_SVC_VP,
A.SVC_AREA,
A.SOLD_SEGMENT,
A.SOLD_BRANCH,
A.SOLD_DIRECTOR,
A.SOLD_JR_SALES_VP,
A.SOLD_SR_SALES_VP,
A.PROV_CENTER,
B.HSC,
A.PROVISIONER,
A.PROV_MANAGER,
A.PROV_SR_MANAGER,
A.PROV_DIRECTOR,
A.PROV_EXEC_DIRECTOR,
A.TEST_INSTALL_CENTER,
C.INSTALL_TECH,
C.INSTALL_MANAGER,
A.TEST_INSTALL_SR_MANAGER,
A.TEST_INSTALL_DIRECTOR,
A.TEST_INSTALL_EXEC_DIRECTOR,
A.IM_CENTER,
A.IM_NAME,
A.IM_SUP,
A.IM_MANAGER_NAME,
A.IM_GRP_MANAGER,
A.IM_DIRECTOR,
A.IM_VP,
A.SERVICE_LOOKUP,
A.ETH_TYPE,
A.CKT_TYPE,
A.CKT_TYPE2,
(CASE WHEN (A.REPORT_DATE = A.DATE_CRDD
AND A.STATUS = 'PEND'
) THEN 'MISSED'
WHEN A.STATUS = 'PEND' THEN 'PEND'
WHEN A.STATUS = 'CANC' THEN 'CANC'
WHEN A.MISS_MET_CRDD = 'EXEMPT' THEN 'EXEMPT'
WHEN A.DATE_CRDD < A.DATE_BCOM THEN 'MISSED'
ELSE 'MET'
END) AS ACTUAL_CDDD,
C.INSTALL_TECH_ASGN_DATE,
(CASE WHEN A.DATE_CMIT IS NULL THEN ''
WHEN ENTERPRISE.UTIL.NETWORKDAYS(A.REPORT_DATE, A.DATE_CMIT) - 1 > 15
THEN '16+'
WHEN ENTERPRISE.UTIL.NETWORKDAYS(A.REPORT_DATE, A.DATE_CMIT) - 1 > 10
THEN '11-15'
WHEN ENTERPRISE.UTIL.NETWORKDAYS(A.REPORT_DATE, A.DATE_CMIT) - 1 > 5
THEN '6-10'
WHEN ENTERPRISE.UTIL.NETWORKDAYS(A.REPORT_DATE, A.DATE_CMIT) - 1 > 05
THEN '0-5'
ELSE 'PAST DUE'
END) AS COMMIT_BUCKET,
(CASE WHEN (A.REPORT_DATE = A.DATE_CRDD
AND A.STATUS = 'PEND'
AND A.STATUS = 'COMP'
)
AND ENTERPRISE.UTIL.NETWORKDAYS(A.DATE_CRDD, A.DATE_BCOM)
- 1 < 11 THEN 'Y'
ELSE 'N'
END) AS OVER_10,
(CASE WHEN A.DATE_CMIT IS NULL THEN ''
WHEN A.DATE_CRDD > A.DATE_CMIT THEN 'CRDD>COMMIT'
ELSE ''
END) AS CRDD_GREATER_THAN_COMMIT,
A.MAX_ER_NO,
A.MAX_ER_RFT,
(CASE WHEN A.MAX_ER_RFT > A.DATE_CRDD THEN 'Y'
ELSE ''
END) AS ER_GREATER_THAN_CRDD,
C.BLANK,
C.BLANK,
CSCH_TO_CDDD,
-- This is the new BUCKET
(CASE WHEN CSCH_TO_CDDD < 0 THEN '<0'
WHEN CSCH_TO_CDDD BETWEEN -1 AND 6 THEN '<=5'
WHEN CSCH_TO_CDDD > 5 THEN '>5'
ELSE ''
END) AS BUCKET
FROM ERP.BASELINE_PPD_PIVOT_HISTORY_TBL A
LEFT JOIN TPEARCE.HSC_TABLE B
ON A.ORD_NUM = B.ORDER_NO
LEFT JOIN TPEARCE.WFM_TABLES C
ON A.ORD_NUM = C.ORDER_NUMBER
-- This has moved from the SELECT to a CROSS JOIN
CROSS JOIN (SELECT CASE WHEN C.INSTALL_TECH_ASGN_DATE = A.DATE_CRDD THEN 0
WHEN C.INSTALL_TECH_ASGN_DATE > A.DATE_CRDD
THEN ENTERPRISE.UTIL.NETWORKDAYS(A.DATE_CRDD,
C.INSTALL_TECH_ASGN_DATE)
- 1
WHEN C.INSTALL_TECH_ASGN_DATE < A.DATE_CRDD
THEN ENTERPRISE.UTIL.NETWORKDAYS(C.INSTALL_TECH_ASGN_DATE,
A.DATE_CRDD) - 1
WHEN A.STATUS = 'PENDING' THEN NULL
ELSE NULL
END
) AS X (CSCH_TO_CDDD)
WHERE REPORT_DATE = '17-may-2011'
AND JR_SVC_VP = 'SMITH'
AND PROV_SR_MANAGER = 'SMITH'
AND REPORT_TYPE = 'D'
AND ORD_TYPE <> 'O'
AND BILLABLE = 'Y'
AND PRODUCT <> 'IDE'
AND A.STATUS <> 'CANC'
OR REPORT_DATE = '17-may-2011'
AND JR_SVC_VP = 'SMITH'
AND PROV_SR_MANAGER = 'SMITH'
AND REPORT_TYPE = 'D'
AND ORD_TYPE <> 'O'
AND BILLABLE = 'Y'
AND PRODUCT = 'IDE'
AND SPEED <> 'DS3'
AND SPEED <> 'OC3'
AND A.STATUS <> 'CANC'
OR REPORT_DATE = '17-may-2011'
AND JR_SVC_VP = 'SMITH'
AND PROV_SR_MANAGER = 'SMITH'
AND REPORT_TYPE = 'D'
AND ORD_TYPE <> 'O'
AND BILLABLE = 'Y'
AND PRODUCT = 'IDE'
AND SPEED = 'DS3'
AND SPEED = 'OC3'
AND A.STATUS <> 'CANC'
AND DATE_STRT < '13-mar-2010'
GROUP BY A.REPORT_DATE,
A.REPORT_TYPE,
A.ORD_NUM,
A.CKT_QTY,
A.SUPP_VER,
A.ORD_TYPE,
A.ORDERPRO_TIN,
A.BILLABLE,
A.VENDOR,
A.E_MRV_FLAG,
A.MRV,
A.ORDER_EXPEDITE,
A.ORDER_EXPEDITE_TYPE,
A.PROJECT,
A.NASPID,
A.CONV_NASPID,
A.CUST_NAME,
A.REGION,
A.DOMESTIC_INTL,
A.CPE_FLAG,
A.MNGD_FLAG,
A.SYSTEM,
A.PRODUCT,
A.SPEED,
A.SPEED_CAT,
PROV_SUPERVISOR,
A.PRODUCT_FAMILY,
A.NEW_TELCO,
A.STATUS,
A.PEND_RSN,
A.POM_STATUS,
A.POM_SUBSTATUS,
A.ACTIVE_FLAG,
A.BASELINE_DESC,
A.DATE_STRT,
A.DATE_CRDD,
A.DATE_CMIT,
A.DATE_ICOM,
A.DATE_BCOM,
A.ORD_BD,
A.CRDD_BD,
A.TARGET,
A.NEW_PPD_FLAG,
A.MISS_MET_COMMIT,
A.MISS_MET_CRDD,
A.MISS_MET_TARGET,
A.CENTER,
A.JR_SVC_VP,
SVC_AREA,
SOLD_SEGMENT,
A.SOLD_BRANCH,
A.SOLD_DIRECTOR,
A.SOLD_JR_SALES_VP,
A.SOLD_SR_SALES_VP,
A.PROV_CENTER,
B.HSC,
A.PROVISIONER,
A.PROV_MANAGER,
A.PROV_SR_MANAGER,
A.PROV_DIRECTOR,
A.PROV_EXEC_DIRECTOR,
A.TEST_INSTALL_CENTER,
C.INSTALL_TECH,
C.INSTALL_MANAGER,
A.TEST_INSTALL_SR_MANAGER,
A.TEST_INSTALL_DIRECTOR,
A.TEST_INSTALL_EXEC_DIRECTOR,
A.IM_CENTER,
A.IM_NAME,
A.IM_SUP,
A.IM_MANAGER_NAME,
A.IM_GRP_MANAGER,
A.IM_DIRECTOR,
A.IM_VP,
A.SERVICE_LOOKUP,
A.ETH_TYPE,
A.CKT_TYPE,
A.CKT_TYPE2,
C.INSTALL_TECH_ASGN_DATE,
A.MAX_ER_NO,
A.MAX_ER_RFT,
C.BLANK,
C.BLANK
However, you seem to have some other issues in that code: did you notice the AND/OR mix in the WHERE clause?
Probably this is not what you want to do, you would rather sorround some predicates with brackets.
Hope this helps
Gianluca
-- Gianluca Sartori
May 20, 2011 at 9:33 am
Thank you to everyone for the assistance. I'm so sorry if I wasted anyone's time. I certainly appreciate all the effort on this.
Viewing 5 posts - 16 through 19 (of 19 total)
You must be logged in to reply to this topic. Login to reply