Search code examples
sqldatabasedb2correlated-subquery

Getting the multiple count() in a sinle select statement without script repitiion

Below is a script to get the count in a single select with lot of code redundancy. How really I can clean up the code?

SELECT

  (SELECT COUNT(CHH.TRANSACTION_TYPE) FROM HW_PROD.ILCCHH CHH INNER JOIN HW_PROD.ILCOHD OHD ON CHH.CONTROL_NUMBER = OHD.CONTROL_NUMBER AND CHH.PICK_TICKET = OHD.TICKET_NUMBER AND CHH.CHHDC = OHD.OHDDC AND CHH.WHSE_IDENTITY = OHD.WHSE_IDENTITY WHERE CHH.TRANSACTION_TYPE='PR' AND ((LC_EXCLUDE_FLAG = 'Y' AND CHH.CHHWSE <> LC_EXCLUDE_WHSEPLANT) OR  ((LC_EXCLUDE_FLAG = '' AND CHH.CHHWSE = LC_EXCLUDE_WHSEPLANT))) AND CHH.CLIENT_IDENTITY= LN_CLIENT_IDENTITY AND CHH.WHSE_IDENTITY = LN_WHSE_IDENTITY AND CHH.ADD_DATE = CURRENT_DATE ) AS PR_COUNT_DAY_ONE,
  (SELECT COUNT(CHH.TRANSACTION_TYPE) FROM HW_PROD.ILCCHH CHH INNER JOIN HW_PROD.ILCOHD OHD ON CHH.CONTROL_NUMBER = OHD.CONTROL_NUMBER AND CHH.PICK_TICKET = OHD.TICKET_NUMBER AND CHH.CHHDC = OHD.OHDDC AND CHH.WHSE_IDENTITY = OHD.WHSE_IDENTITY WHERE CHH.TRANSACTION_TYPE='SR' AND ((LC_EXCLUDE_FLAG = 'Y' AND CHH.CHHWSE <> LC_EXCLUDE_WHSEPLANT) OR  ((LC_EXCLUDE_FLAG = '' AND CHH.CHHWSE = LC_EXCLUDE_WHSEPLANT))) AND CHH.CLIENT_IDENTITY= LN_CLIENT_IDENTITY AND CHH.WHSE_IDENTITY = LN_WHSE_IDENTITY AND CHH.ADD_DATE = CURRENT_DATE ) AS SR_COUNT_DAY_ONE,
  (SELECT COUNT(CHH.TRANSACTION_TYPE) FROM HW_PROD.ILCCHH CHH INNER JOIN HW_PROD.ILCOHD OHD ON CHH.CONTROL_NUMBER = OHD.CONTROL_NUMBER AND CHH.PICK_TICKET = OHD.TICKET_NUMBER AND CHH.CHHDC = OHD.OHDDC AND CHH.WHSE_IDENTITY = OHD.WHSE_IDENTITY WHERE CHH.TRANSACTION_TYPE='EX' AND ((LC_EXCLUDE_FLAG = 'Y' AND CHH.CHHWSE <> LC_EXCLUDE_WHSEPLANT) OR  ((LC_EXCLUDE_FLAG = '' AND CHH.CHHWSE = LC_EXCLUDE_WHSEPLANT))) AND CHH.CLIENT_IDENTITY= LN_CLIENT_IDENTITY AND CHH.WHSE_IDENTITY = LN_WHSE_IDENTITY AND CHH.ADD_DATE = CURRENT_DATE ) AS EX_COUNT_DAY_ONE,
  (SELECT COUNT(CHH.TRANSACTION_TYPE) FROM HW_PROD.ILCCHH CHH INNER JOIN HW_PROD.ILCOHD OHD ON CHH.CONTROL_NUMBER = OHD.CONTROL_NUMBER AND CHH.PICK_TICKET = OHD.TICKET_NUMBER AND CHH.CHHDC = OHD.OHDDC AND CHH.WHSE_IDENTITY = OHD.WHSE_IDENTITY WHERE CHH.TRANSACTION_TYPE='01' AND ((LC_EXCLUDE_FLAG = 'Y' AND CHH.CHHWSE <> LC_EXCLUDE_WHSEPLANT) OR  ((LC_EXCLUDE_FLAG = '' AND CHH.CHHWSE = LC_EXCLUDE_WHSEPLANT))) AND CHH.CLIENT_IDENTITY= LN_CLIENT_IDENTITY AND CHH.WHSE_IDENTITY = LN_WHSE_IDENTITY AND CHH.ADD_DATE = CURRENT_DATE ) AS RE_COUNT_DAY_ONE,
  (SELECT COUNT(CHH.TRANSACTION_TYPE) FROM HW_PROD.ILCCHH CHH INNER JOIN HW_PROD.ILCOHD OHD ON CHH.CONTROL_NUMBER = OHD.CONTROL_NUMBER AND CHH.PICK_TICKET = OHD.TICKET_NUMBER AND CHH.CHHDC = OHD.OHDDC AND CHH.WHSE_IDENTITY = OHD.WHSE_IDENTITY WHERE CHH.TRANSACTION_TYPE='33' AND ((LC_EXCLUDE_FLAG = 'Y' AND CHH.CHHWSE <> LC_EXCLUDE_WHSEPLANT) OR  ((LC_EXCLUDE_FLAG = '' AND CHH.CHHWSE = LC_EXCLUDE_WHSEPLANT))) AND CHH.CLIENT_IDENTITY= LN_CLIENT_IDENTITY AND CHH.WHSE_IDENTITY = LN_WHSE_IDENTITY AND CHH.ADD_DATE = CURRENT_DATE ) AS TR_COUNT_DAY_ONE,

  (SELECT COUNT(CHH.TRANSACTION_TYPE) FROM HW_PROD.ILCCHH CHH INNER JOIN HW_PROD.ILCOHD OHD ON CHH.CONTROL_NUMBER = OHD.CONTROL_NUMBER AND CHH.PICK_TICKET = OHD.TICKET_NUMBER AND CHH.CHHDC = OHD.OHDDC AND CHH.WHSE_IDENTITY = OHD.WHSE_IDENTITY WHERE CHH.TRANSACTION_TYPE='PR' AND ((LC_EXCLUDE_FLAG = 'Y' AND CHH.CHHWSE <> LC_EXCLUDE_WHSEPLANT) OR  ((LC_EXCLUDE_FLAG = '' AND CHH.CHHWSE = LC_EXCLUDE_WHSEPLANT))) AND CHH.CLIENT_IDENTITY= LN_CLIENT_IDENTITY AND CHH.WHSE_IDENTITY = LN_WHSE_IDENTITY AND CHH.ADD_DATE =  CURRENT_DATE - 1 DAYS) AS PR_COUNT_DAY_TWO,
  (SELECT COUNT(CHH.TRANSACTION_TYPE) FROM HW_PROD.ILCCHH CHH INNER JOIN HW_PROD.ILCOHD OHD ON CHH.CONTROL_NUMBER = OHD.CONTROL_NUMBER AND CHH.PICK_TICKET = OHD.TICKET_NUMBER AND CHH.CHHDC = OHD.OHDDC AND CHH.WHSE_IDENTITY = OHD.WHSE_IDENTITY WHERE CHH.TRANSACTION_TYPE='SR' AND ((LC_EXCLUDE_FLAG = 'Y' AND CHH.CHHWSE <> LC_EXCLUDE_WHSEPLANT) OR  ((LC_EXCLUDE_FLAG = '' AND CHH.CHHWSE = LC_EXCLUDE_WHSEPLANT))) AND CHH.CLIENT_IDENTITY= LN_CLIENT_IDENTITY AND CHH.WHSE_IDENTITY = LN_WHSE_IDENTITY AND CHH.ADD_DATE =  CURRENT_DATE - 1 DAYS) AS SR_COUNT_DAY_TWO,
  (SELECT COUNT(CHH.TRANSACTION_TYPE) FROM HW_PROD.ILCCHH CHH INNER JOIN HW_PROD.ILCOHD OHD ON CHH.CONTROL_NUMBER = OHD.CONTROL_NUMBER AND CHH.PICK_TICKET = OHD.TICKET_NUMBER AND CHH.CHHDC = OHD.OHDDC AND CHH.WHSE_IDENTITY = OHD.WHSE_IDENTITY WHERE CHH.TRANSACTION_TYPE='EX' AND ((LC_EXCLUDE_FLAG = 'Y' AND CHH.CHHWSE <> LC_EXCLUDE_WHSEPLANT) OR  ((LC_EXCLUDE_FLAG = '' AND CHH.CHHWSE = LC_EXCLUDE_WHSEPLANT))) AND CHH.CLIENT_IDENTITY= LN_CLIENT_IDENTITY AND CHH.WHSE_IDENTITY = LN_WHSE_IDENTITY AND CHH.ADD_DATE =  CURRENT_DATE - 1 DAYS) AS EX_COUNT_DAY_TWO,
  (SELECT COUNT(CHH.TRANSACTION_TYPE) FROM HW_PROD.ILCCHH CHH INNER JOIN HW_PROD.ILCOHD OHD ON CHH.CONTROL_NUMBER = OHD.CONTROL_NUMBER AND CHH.PICK_TICKET = OHD.TICKET_NUMBER AND CHH.CHHDC = OHD.OHDDC AND CHH.WHSE_IDENTITY = OHD.WHSE_IDENTITY WHERE CHH.TRANSACTION_TYPE='01' AND ((LC_EXCLUDE_FLAG = 'Y' AND CHH.CHHWSE <> LC_EXCLUDE_WHSEPLANT) OR  ((LC_EXCLUDE_FLAG = '' AND CHH.CHHWSE = LC_EXCLUDE_WHSEPLANT))) AND CHH.CLIENT_IDENTITY= LN_CLIENT_IDENTITY AND CHH.WHSE_IDENTITY = LN_WHSE_IDENTITY AND CHH.ADD_DATE =  CURRENT_DATE - 1 DAYS) AS RE_COUNT_DAY_TWO,
  (SELECT COUNT(CHH.TRANSACTION_TYPE) FROM HW_PROD.ILCCHH CHH INNER JOIN HW_PROD.ILCOHD OHD ON CHH.CONTROL_NUMBER = OHD.CONTROL_NUMBER AND CHH.PICK_TICKET = OHD.TICKET_NUMBER AND CHH.CHHDC = OHD.OHDDC AND CHH.WHSE_IDENTITY = OHD.WHSE_IDENTITY WHERE CHH.TRANSACTION_TYPE='33' AND ((LC_EXCLUDE_FLAG = 'Y' AND CHH.CHHWSE <> LC_EXCLUDE_WHSEPLANT) OR  ((LC_EXCLUDE_FLAG = '' AND CHH.CHHWSE = LC_EXCLUDE_WHSEPLANT))) AND CHH.CLIENT_IDENTITY= LN_CLIENT_IDENTITY AND CHH.WHSE_IDENTITY = LN_WHSE_IDENTITY AND CHH.ADD_DATE =  CURRENT_DATE - 1 DAYS) AS TR_COUNT_DAY_TWO

FROM MYSCHEMA.DUAL;
Solution

  • Something like:

    select CHH.ADD_DATE, CHH.TRANSACTION_TYPE, count(1)
from HW_PROD.ILCCHH CHH 
JOIN HW_PROD.ILCOHD OHD 
    ON CHH.CONTROL_NUMBER = OHD.CONTROL_NUMBER 
   AND CHH.PICK_TICKET = OHD.TICKET_NUMBER 
   AND CHH.CHHDC = OHD.OHDDC 
   AND CHH.WHSE_IDENTITY = OHD.WHSE_IDENTITY
WHERE CHH.TRANSACTION_TYPE IN ('PR','SR','EX','01','33')
  AND CHH.ADD_DATE IN (CURRENT_DATE, CURRENT_DATE - 1 DAYS) 
  AND <remaining where clause>
GROUP BY CHH.ADD_DATE, CHH.TRANSACTION_TYPE

    Now it is a matter of transposing this, but that is preferably done in another layer of your application.

    Another solution is:

    SELECT COUNT( CASE WHEN CHH.TRANSACTION_TYPE = 'PR' 
                   AND CHH.ADD_DATE = CURRENT_DATE
                  THEN 1
             END ) AS PR_COUNT_DAY_ONE
     , COUNT( CASE WHEN CHH.TRANSACTION_TYPE = 'SR' 
                   AND CHH.ADD_DATE = CURRENT_DATE
                  THEN 1
             END ) AS SR_COUNT_DAY_ONE
    , ...
FROM HW_PROD.ILCCHH CHH 
JOIN HW_PROD.ILCOHD OHD 
    ON CHH.CONTROL_NUMBER = OHD.CONTROL_NUMBER 
   ...
WHERE CHH.TRANSACTION_TYPE IN ('PR','SR','EX','01','33')
 AND CHH.ADD_DATE IN (CURRENT_DATE, CURRENT_DATE - 1 DAYS)
 AND ...