Union alternative in Teradata

Database
Enthusiast

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

Tags (3)
1 REPLY
Senior Apprentice

Re: Union alternative in Teradata

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