Re: How to retrieve only one case from case statement

Also note Dieter's observation that if you really intend LEFT JOIN to ITEM_SCAN_EVENT_FCT, you will need to move the filter condition for that table from the WHERE clause to the ON clause.

Select
SUBSTR(PARCEL_SEM_VW.ITEM_SMRY.RECEIVER_POSTAL_CODE,1 , 3) as Dest_FSA,PARCEL_SEM_VW.ITEM_SMRY.ASSOCIATED_PIN as Associated_Pins,PARCEL_SEM_VW.ITEM_SMRY.ITEM_ID as item_no,PARCEL_SEM_VW.ITEM_SCAN_EVENT_FCT.ASSOCIATED_PIN as Scan_evnt_Pins,PARCEL_SEM_VW.ITEM_SMRY.CREATE_DTM as snapdate, PARCEL_SEM_VW.ITEM_SMRY.L_SCAN_DTM,PARCEL_SEM_VW.ITEM_SMRY.CPC_INDUCTION_DTM,PARCEL_SEM_VW.SCAN_EVENT_MAS.SCAN_EVENT_DESC_EN,PARCEL_SEM_VW.ITEM_SCAN_EVENT_FCT.SCAN_EVENT_CODE as scan_code,PARCEL_SEM_VW.ITEM_SCAN_EVENT_FCT.SCAN_DTM_LOCAL,EDW_SEM_VW.WC_CC_VW.WORK_CENTRE_ID as WC_ID,EDW_SEM_VW.WC_CC_VW.WORK_CENTRE_NAME_EN as Work_Center_Name,EDW_SEM_VW.WC_CC_VW.WORK_CENTRE_TYPE_DESC_EN as Work_Center_Categ,EDW_SEM_VW.WC_CC_VW.SITE_NAME_DESC_EN as Site_Name,EDW_SEM_VW.WC_CC_VW.COST_CENTRE_NAME_EN_SHORT as Cost_Center,EDW_SEM_VW.WC_CC_VW.SAP_COST_CENTRE_ID as CC_ID,
SUM(CASE WHEN ((SUCCESSFUL_DELIVERY_DTM IS NULL and ATT_SUCCESSFUL_DELIVERY_DTM IS NULL) and (UPDATED_EXPECTED_DEL_DTE < current_date) and (L_SCAN_DTM < Date-1))THEN 1 ELSE 0 END) as Missing_count
FROM PARCEL_SEM_VW.ITEM_SMRY
Left JOIN PARCEL_SEM_VW.ITEM_SCAN_EVENT_FCT on PARCEL_SEM_VW.ITEM_SMRY.ASSOCIATED_PIN = PARCEL_SEM_VW.ITEM_SCAN_EVENT_FCT.ASSOCIATED_PIN/* should this condition be moved here? and (ITEM_SCAN_EVENT_FCT.SCAN_DTM_LOCAL between current_date -10 and current_date) */
Left JOIN PARCEL_SEM_VW.SCAN_EVENT_MAS ON PARCEL_SEM_VW.ITEM_SCAN_EVENT_FCT.SCAN_EVENT_CODE=PARCEL_SEM_VW.SCAN_EVENT_MAS.SCAN_EVENT_CODE
Left JOIN EDW_SEM_VW.WC_CC_VW ON PARCEL_SEM_VW.ITEM_SMRY.L_SCAN_WC=EDW_SEM_VW.WC_CC_VW.SAP_WORK_CENTRE_ID
WHERE ITEM_SMRY.CREATE_DTM is NOT NULL and (ITEM_SMRY.CREATE_DTM between current_date -10 and current_date) /* and removed from here? */ and (ITEM_SCAN_EVENT_FCT.SCAN_DTM_LOCAL between current_date -10 and current_date)
Group by Dest_FSA,Associated_Pins, item_no,Scan_evnt_Pins,snapdate, L_SCAN_DTM,CPC_INDUCTION_DTM,SCAN_EVENT_DESC_EN,scan_code,SCAN_DTM_LOCAL,WC_ID,Work_Center_Name,Work_Center_Categ,Site_Name,Cost_Center,CC_ID
HAVING MISSING_COUNT > 0

Re: How to retrieve only one case from case statement

Also note Dieter's observation that if you really intend LEFT JOIN to ITEM_SCAN_EVENT_FCT, you will need to move the filter condition for that table from the WHERE clause to the ON clause.

Select
SUBSTR(PARCEL_SEM_VW.ITEM_SMRY.RECEIVER_POSTAL_CODE,1 , 3) as Dest_FSA,PARCEL_SEM_VW.ITEM_SMRY.ASSOCIATED_PIN as Associated_Pins,PARCEL_SEM_VW.ITEM_SMRY.ITEM_ID as item_no,PARCEL_SEM_VW.ITEM_SCAN_EVENT_FCT.ASSOCIATED_PIN as Scan_evnt_Pins,PARCEL_SEM_VW.ITEM_SMRY.CREATE_DTM as snapdate, PARCEL_SEM_VW.ITEM_SMRY.L_SCAN_DTM,PARCEL_SEM_VW.ITEM_SMRY.CPC_INDUCTION_DTM,PARCEL_SEM_VW.SCAN_EVENT_MAS.SCAN_EVENT_DESC_EN,PARCEL_SEM_VW.ITEM_SCAN_EVENT_FCT.SCAN_EVENT_CODE as scan_code,PARCEL_SEM_VW.ITEM_SCAN_EVENT_FCT.SCAN_DTM_LOCAL,EDW_SEM_VW.WC_CC_VW.WORK_CENTRE_ID as WC_ID,EDW_SEM_VW.WC_CC_VW.WORK_CENTRE_NAME_EN as Work_Center_Name,EDW_SEM_VW.WC_CC_VW.WORK_CENTRE_TYPE_DESC_EN as Work_Center_Categ,EDW_SEM_VW.WC_CC_VW.SITE_NAME_DESC_EN as Site_Name,EDW_SEM_VW.WC_CC_VW.COST_CENTRE_NAME_EN_SHORT as Cost_Center,EDW_SEM_VW.WC_CC_VW.SAP_COST_CENTRE_ID as CC_ID,
SUM(CASE WHEN ((SUCCESSFUL_DELIVERY_DTM IS NULL and ATT_SUCCESSFUL_DELIVERY_DTM IS NULL) and (UPDATED_EXPECTED_DEL_DTE < current_date) and (L_SCAN_DTM < Date-1))THEN 1 ELSE 0 END) as Missing_count
FROM PARCEL_SEM_VW.ITEM_SMRY
Left JOIN PARCEL_SEM_VW.ITEM_SCAN_EVENT_FCT on PARCEL_SEM_VW.ITEM_SMRY.ASSOCIATED_PIN = PARCEL_SEM_VW.ITEM_SCAN_EVENT_FCT.ASSOCIATED_PIN/* should this condition be moved here? and (ITEM_SCAN_EVENT_FCT.SCAN_DTM_LOCAL between current_date -10 and current_date) */
Left JOIN PARCEL_SEM_VW.SCAN_EVENT_MAS ON PARCEL_SEM_VW.ITEM_SCAN_EVENT_FCT.SCAN_EVENT_CODE=PARCEL_SEM_VW.SCAN_EVENT_MAS.SCAN_EVENT_CODE
Left JOIN EDW_SEM_VW.WC_CC_VW ON PARCEL_SEM_VW.ITEM_SMRY.L_SCAN_WC=EDW_SEM_VW.WC_CC_VW.SAP_WORK_CENTRE_ID
WHERE ITEM_SMRY.CREATE_DTM is NOT NULL and (ITEM_SMRY.CREATE_DTM between current_date -10 and current_date) /* and removed from here? */ and (ITEM_SCAN_EVENT_FCT.SCAN_DTM_LOCAL between current_date -10 and current_date)
Group by Dest_FSA,Associated_Pins, item_no,Scan_evnt_Pins,snapdate, L_SCAN_DTM,CPC_INDUCTION_DTM,SCAN_EVENT_DESC_EN,scan_code,SCAN_DTM_LOCAL,WC_ID,Work_Center_Name,Work_Center_Categ,Site_Name,Cost_Center,CC_ID
HAVING MISSING_COUNT > 0