How to retrieve only once case from case statement

Database
Highlighted

How to retrieve only once case from case statement

Hello All,

 

Sample: I have a query which counts every single package based on the following case statement.

Example:

SUM(CASE WHEN ((DATE1 IS NULL  and DATE2 IS NULL) and (UPDATED_EXPECTED_DATE < current_date) and (L_SCAN < Date-1))THEN 1 ELSE 0 END) as Missing_count

   Result Set:

     Package Name   Missing

1)    ABC1                    1

2)    ABC2                    1

3)    ABC3                    0

4)    ABC4                    0

 

My question: Is there a way I could write something in the query just to get packages with "1" and not "0".

 

Appreciate your responses.

Abhi 

 

 


Accepted Solutions
Teradata Employee

Re: How to retrieve only one case from case statement

HAVING comes after GROUP BY, not in WHERE clause.

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
1 ACCEPTED SOLUTION
8 REPLIES
Teradata Employee

Re: How to retrieve only once case from case statement

Use a Qualify phrase at the end of the query: Qualify Missing_count > 0

Re: How to retrieve only once case from case statement

Appreciate your reponse.

 

This is my query:

I tried to place it in my where clause but it doesnt work.

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

 

Teradata Employee

Re: How to retrieve only once case from case statement

The Teradata SQL DML manual can be found at info.teradata.com.  The 3rd page of Chapter 1 shows that Qualify follows Group By.  It's not in the where clause.

Teradata Employee

Re: How to retrieve only once case from case statement

GROUP BY ....

HAVING MISSING_COUNT > 0

Re: How to retrieve only once case from case statement

 

Hello Fred appreciate your response..,



I tried to have 

GROUP BY ....

HAVING MISSING_COUNT > 0 but it still doesnt work.



This is my query:

 

 

 

 

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

 

Junior Contributor

Re: How to retrieve only once case from case statement

The syntax is correct., so what exactly does "doesnt work" mean? 

 

Btw, are you sure you get the expected result?

This WHERE-condition

(ITEM_SCAN_EVENT_FCT.SCAN_DTM_LOCAL between current_date -10 and current_date)

turns the Left join into an Inner join, check Explan.

Re: How to retrieve only one case from case statement

Hi Dnoeth,

 

I tried to place "HAVING MISSING_COUNT < 0" in my where clause and it says "Improper use of an aggregate function in a where clause".

Teradata Employee

Re: How to retrieve only one case from case statement

HAVING comes after GROUP BY, not in WHERE clause.

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