All Forums Database
TcognosD 11 posts Joined 04/13
28 Oct 2013
Union alternative in Teradata

Can following query be rewritten so it does not have to use UNION ?  THANKS.
 
SELECT 1 AS FCST_MONTH_KEY, SUM(s.MDU_Plan), SUM(s.Bulk_Plan), SUM(s.DTH_Plan), SUM(s.CP_Plan), SUM(s.D2_Lite_Plan), SUM(s.UNKNOWNPLAN)

FROM 

(SELECT 1 AS FCST_MONTH_KEY, SUM(Bulk+DTH+CP+D2_Lite+UNKNOWNPLAN) AS MDU_Plan, SUM(Bulk) AS Bulk_Plan, SUM(DTH) AS DTH_Plan, SUM(CP) AS CP_Plan, SUM(D2_Lite) AS D2_Lite_Plan, SUM(UNKNOWNPLAN) AS UNKNOWNPLAN

FROM (

SELECT  FCST_MONTH_KEY,  --MDU_VERT_MKT_TYPE_CODE,  

(CASE WHEN MDU_VERT_MKT_TYPE_CODE = 'Bulk' 

THEN  (MDU_VERT_MKT_ACCT_FCST_CNT+0.01) ELSE 0

END)   AS Bulk,

(CASE WHEN MDU_VERT_MKT_TYPE_CODE = 'DTH' 

THEN  (MDU_VERT_MKT_ACCT_FCST_CNT+0.01) ELSE 0

END) AS DTH,

(CASE WHEN MDU_VERT_MKT_TYPE_CODE = 'CP' 

THEN  (MDU_VERT_MKT_ACCT_FCST_CNT+0.01) ELSE 0

END) AS CP,

(CASE WHEN MDU_VERT_MKT_TYPE_CODE = 'D2 Lite' 

THEN  (MDU_VERT_MKT_ACCT_FCST_CNT+0.01) ELSE 0

END) AS D2_Lite,

(CASE WHEN MDU_VERT_MKT_TYPE_CODE NOT IN ( 'DTH' , 'D2 Lite' , 'CP' ,'Bulk' )

THEN  (MDU_VERT_MKT_ACCT_FCST_CNT+0.01) ELSE 0

END) AS UNKNOWNPLAN,

 

 ( (MDU_VERT_MKT_ACCT_FCST_CNT+0.01)/ EXTRACT(DAY FROM ADD_MONTHS(CURRENT_DATE-EXTRACT(DAY FROM CURRENT_DATE),1)) )* EXTRACT(DAY FROM CURRENT_DATE-1) AS MDU_VERT_MKT_ACCT_FCST_CNT

FROM MDU_VERT_MKT_FCST_MTH_UMT

WHERE FCST_MONTH_KEY < EXTRACT(MONTH FROM CURRENT_DATE)

 

) a 

GROUP BY 1

 

 

UNION 

 

 

SELECT FCST_MONTH_KEY, SUM(Bulk+DTH+CP+D2_Lite+UNKNOWNPLAN) AS MDU_Plan, SUM(Bulk) AS Bulk_Plan, SUM(DTH) AS DTH_Plan, SUM(CP) AS CP_Plan, SUM(D2_Lite) AS D2_Lite_Plan, SUM(UNKNOWNPLAN) AS UNKNOWNPLAN

FROM (

SELECT  FCST_MONTH_KEY,  --MDU_VERT_MKT_TYPE_CODE,  

(CASE WHEN MDU_VERT_MKT_TYPE_CODE = 'Bulk' 

THEN ( (MDU_VERT_MKT_ACCT_FCST_CNT+0.01)/ EXTRACT(DAY FROM ADD_MONTHS(CURRENT_DATE-EXTRACT(DAY FROM CURRENT_DATE),1)) )* EXTRACT(DAY FROM CURRENT_DATE-1) --AS MDU_VERT_MKT_ACCT_FCST_CNT

ELSE 0

END)   AS Bulk,

(CASE WHEN MDU_VERT_MKT_TYPE_CODE = 'DTH' 

THEN ( (MDU_VERT_MKT_ACCT_FCST_CNT+0.01)/ EXTRACT(DAY FROM ADD_MONTHS(CURRENT_DATE-EXTRACT(DAY FROM CURRENT_DATE),1)) )* EXTRACT(DAY FROM CURRENT_DATE-1) 

ELSE 0

END) AS DTH,

(CASE WHEN MDU_VERT_MKT_TYPE_CODE = 'CP' 

THEN ( (MDU_VERT_MKT_ACCT_FCST_CNT+0.01)/ EXTRACT(DAY FROM ADD_MONTHS(CURRENT_DATE-EXTRACT(DAY FROM CURRENT_DATE),1)) )* EXTRACT(DAY FROM CURRENT_DATE-1) 

ELSE 0

END) AS CP,

(CASE WHEN MDU_VERT_MKT_TYPE_CODE = 'D2 Lite' 

THEN ( (MDU_VERT_MKT_ACCT_FCST_CNT+0.01)/ EXTRACT(DAY FROM ADD_MONTHS(CURRENT_DATE-EXTRACT(DAY FROM CURRENT_DATE),1)) )* EXTRACT(DAY FROM CURRENT_DATE-1) 

ELSE 0

END) AS D2_Lite,

(CASE WHEN MDU_VERT_MKT_TYPE_CODE NOT IN ( 'DTH' , 'D2 Lite' , 'CP' ,'Bulk' )

THEN ( (MDU_VERT_MKT_ACCT_FCST_CNT+0.01)/ EXTRACT(DAY FROM ADD_MONTHS(CURRENT_DATE-EXTRACT(DAY FROM CURRENT_DATE),1)) )* EXTRACT(DAY FROM CURRENT_DATE-1) 

ELSE 0

END) AS UNKNOWNPLAN,

 

 ( (MDU_VERT_MKT_ACCT_FCST_CNT+0.01)/ EXTRACT(DAY FROM ADD_MONTHS(CURRENT_DATE-EXTRACT(DAY FROM CURRENT_DATE),1)) )* EXTRACT(DAY FROM CURRENT_DATE-1) AS MDU_VERT_MKT_ACCT_FCST_CNT

FROM MDU_VERT_MKT_FCST_MTH_UMT

WHERE FCST_MONTH_KEY = EXTRACT(MONTH FROM CURRENT_DATE)

) b 

GROUP BY 1

) S

GROUP BY 1

 

dnoeth 4628 posts Joined 11/04
28 Oct 2013

This is hardly readable code, you should add it using the "code snippet" icon.
After a quick format you seem to need two levels of aggregation, which is easily done using GROUPING SETS:

SELECT
  COALESCE(FCST_MONTH_KEY, 1)
  ,SUM(Bulk + DTH + CP + D2_Lite + UNKNOWNPLAN) AS MDU_Plan
  ,SUM(Bulk) AS Bulk_Plan
  ,SUM(DTH) AS DTH_Plan
  ,SUM(CP) AS CP_Plan
  ,SUM(D2_Lite) AS D2_Lite_Plan
  ,SUM(UNKNOWNPLAN) AS UNKNOWNPLAN
FROM
  (
    SELECT
      FCST_MONTH_KEY
      ,--MDU_VERT_MKT_TYPE_CODE,  
      (
        CASE
          WHEN MDU_VERT_MKT_TYPE_CODE = 'Bulk'
            THEN ((MDU_VERT_MKT_ACCT_FCST_CNT + 0.01) / EXTRACT(DAY FROM ADD_MONTHS(CURRENT_DATE - EXTRACT(DAY FROM CURRENT_DATE), 1))) * EXTRACT(DAY FROM CURRENT_DATE -1) --AS MDU_VERT_MKT_ACCT_FCST_CNT
          ELSE
            0
        END
      ) AS Bulk
      ,
      (
        CASE
          WHEN MDU_VERT_MKT_TYPE_CODE = 'DTH'
            THEN ((MDU_VERT_MKT_ACCT_FCST_CNT + 0.01) / EXTRACT(DAY FROM ADD_MONTHS(CURRENT_DATE - EXTRACT(DAY FROM CURRENT_DATE), 1))) * EXTRACT(DAY FROM CURRENT_DATE -1)
          ELSE
            0
        END
      ) AS DTH
      ,
      (
        CASE
          WHEN MDU_VERT_MKT_TYPE_CODE = 'CP'
            THEN ((MDU_VERT_MKT_ACCT_FCST_CNT + 0.01) / EXTRACT(DAY FROM ADD_MONTHS(CURRENT_DATE - EXTRACT(DAY FROM CURRENT_DATE), 1))) * EXTRACT(DAY FROM CURRENT_DATE -1)
          ELSE
            0
        END
      ) AS CP
      ,
      (
        CASE
          WHEN MDU_VERT_MKT_TYPE_CODE = 'D2 Lite'
            THEN ((MDU_VERT_MKT_ACCT_FCST_CNT + 0.01) / EXTRACT(DAY FROM ADD_MONTHS(CURRENT_DATE - EXTRACT(DAY FROM CURRENT_DATE), 1))) * EXTRACT(DAY FROM CURRENT_DATE -1)
          ELSE
            0
        END
      ) AS D2_Lite
      ,
      (
        CASE
          WHEN MDU_VERT_MKT_TYPE_CODE NOT IN ('DTH', 'D2 Lite', 'CP', 'Bulk')
            THEN ((MDU_VERT_MKT_ACCT_FCST_CNT + 0.01) / EXTRACT(DAY FROM ADD_MONTHS(CURRENT_DATE - EXTRACT(DAY FROM CURRENT_DATE), 1))) * EXTRACT(DAY FROM CURRENT_DATE -1)
          ELSE
            0
        END
      ) AS UNKNOWNPLAN
      ,((MDU_VERT_MKT_ACCT_FCST_CNT + 0.01) / EXTRACT(DAY FROM ADD_MONTHS(CURRENT_DATE - EXTRACT(DAY FROM CURRENT_DATE), 1))) * EXTRACT(DAY FROM CURRENT_DATE -1) AS MDU_VERT_MKT_ACCT_FCST_CNT
    FROM
      MDU_VERT_MKT_FCST_MTH_UMT
    WHERE
      FCST_MONTH_KEY = EXTRACT(MONTH FROM CURRENT_DATE)
  ) b
GROUP BY GROUPING SETS ((FCST_MONTH_KEY), ())

 Dieter

Dieter

You must sign in to leave a comment.