All Forums Database
BGT 1 post Joined 03/16
08 Mar 2016
Union of tables with different columns

I am new to Teredata and am having an issue with a union with two data sets with slightly different columns.  When I run the below SQL it comes back with a 3625 error for grouping with aggregage functions.  I know the error is caused by the addition of the i.item as ItemNum and the i.MODEL_NO as ModNum but I am not guite sure how to resolve it.  My first shot was adding the following in the secong half of the SQL:  cast (sum (0) as CHAR(5)) as ItemNum,
cast (sum (0) as VARCHAR(12)) as ModNum,
but that doesn't resolve the issue.
Thanks in advance for any help, I am sure that there is a simple solution that I am missing.
 
sel
a.acctg_yr,
a.acctg_qtr,
p.cn_st_prov,
case when p.cn_st_prov in ('DC','IL','IN','KS','KY','MD','MO','OH','OK','VA',' WV') then 'Central' when p.cn_st_prov in ('IA','MI','MN','MT','ND','NE','SD','WI','WY') then 'NorthCentral' when p.cn_st_prov in ('CT','DE','MA','ME','NH','NJ','NY','PA','RI','VT') then 'NorthEast' when p.cn_st_prov in ('ID','OR','WA') then 'NorthWest' when p.cn_st_prov in ('AL','AR','FL','GA','LA','MS','NC','SC','TN','TX') then 'Southeast' when p.cn_st_prov in ('AZ','CO','NM','NV','UT') then 'SouthWest' when p.cn_st_prov in ('AK','CA','HI','PR') then p.cn_st_prov else 'Other' end as StateGroup,
case when a.psv_div_ogp_no in ('006','022','026','046','057','071') then a.psv_div_ogp_no else '099' end as Division,
i.mod_platform as ProductType,
case when c.svc_prd_pln_cd in ('MPA','RPA','BPA','SSA') then c.svc_prd_pln_cd else 'Other' end as PlanType,
c.ma_sell_org as SForAM,
i.item as ItemNum,
i.MODEL_NO as ModNum,
case when p.led_typ_cd in ('RDC','RENM','RENS','CIAQ','TECH','MA2','AMA','TSRV' ,'R1S','RENC','UPS','MA3','PE','IW','CI','MA4','NU',' TECR','REPL','R1M','TCAR','INAC','CISV','CIAE','S',' OTC','CSAT','KMRT','SAVE','DLRV') then p.led_typ_cd else 'Other' end as Lead,
sum(case when id.svc_typ_cd = 'R' then a.call_cnt else 0 end) as recalls_30,
sum(case when id.svc_typ_cd in ('R','Q')  then a.call_cnt else 0 end) as recalls_90,
sum(a.rpr_tm_mit_no) as RepairTime,
sum(a.tvl_tm_mit_no) as TravelTime,
sum(repl_cnt) as CallsWithReplacements,
sum(a.tot_part_qt) as TotalParts,
sum(calls_w_parts) as CallsWithParts,
sum(a.trip_cnt) as TotalTrips,
sum(case when a.call_typ = 'Repair' then a.call_cnt else 0 end) as RepairCalls,
sum(case when a.call_typ = 'PM Check' then a.call_cnt else 0 end) as PMCheckCalls,
sum(a.call_cnt) as TotalCalls,
sum(a.tot_prts_cost) as PartsCost,
sum(a.auth_repl_r) as Unrepairables,
sum(a.fd_ls_adj_f) as FoodLoss,
sum(var_dtr_lvl_cost-comm_pay_exp_a- ovrs_shrts_bd_chks_c- oth_trans_exp_cst_c+semi_var_dtr_lvl_cost- sell_pay_var+cc_ccn_chrg_cst_a+iw_ccn_chrg_cst_a+pa_cc n_chrg_cst_a+tp_ccn_chrg_cst_a+parts_distr_cst_a+serv_ prov_cst_a) as TotalCostsExclComm,
sum(case when a.call_typ = 'Repair' then var_dtr_lvl_cost-comm_pay_exp_a-ovrs_shrts_bd_chks_c- oth_trans_exp_cst_c+semi_var_dtr_lvl_cost- sell_pay_var+cc_ccn_chrg_cst_a+iw_ccn_chrg_cst_a+pa_cc n_chrg_cst_a+tp_ccn_chrg_cst_a+parts_distr_cst_a+serv_ prov_cst_a else 0 end) as RepairCostsExclCommissions,
sum(case when a.call_typ = 'PM Check' then var_dtr_lvl_cost-comm_pay_exp_a-ovrs_shrts_bd_chks_c- oth_trans_exp_cst_c+semi_var_dtr_lvl_cost- sell_pay_var+cc_ccn_chrg_cst_a+iw_ccn_chrg_cst_a+pa_cc n_chrg_cst_a+tp_ccn_chrg_cst_a+parts_distr_cst_a+serv_ prov_cst_a else 0 end) as PMCheckCostsExclCommissions,
sum(tot_dtr_lvl_w_ovhd_cost-comm_pay_exp_a- ovrs_shrts_bd_chks_c-oth_trans_exp_cst_c-sell_pay_var- sell_pay_fix-pa_rev_ovhd_pa-pa_svc_reimb_ovhd_pa) as FullyLoadedCosts,
sum(0) as InForce,
sum(0) as EarnedUnit,
sum(0) as EarnedPremium
from hs_perm_tbls.seth_tech_pm_data_mth4 a
join hs_dw_views.npsxtid id on id.svc_un_no = a.svc_un_fty_id_no and id.so_no = a.svc_ord_no
join hs_perm_tbls.seth_svc_calls c on a.svc_un_fty_id_no = c.svc_un_fty_id_no and a.svc_ord_no = c.svc_ord_no
left join lci_dw_views.npmatsm_cus_svc_mds d on c.hs_cus_no = d.hs_cus_no and c.itm_suf_no = d.itm_suf_no
left join hs_perm_tbls.EngSvcs_prs_item_info_stg i on d.prd_itm_no = i.item and d.psv_div_ogp_no = (i.div-600)
left join hs_perm_tbls.seth_sold_pa3 p on c.hs_cus_no = p.hs_cus_no and c.itm_suf_no = p.itm_suf_no and c.agr_suf_no = p.agr_suf_no
where a.acctg_yr_mth between 201301 and 201511
and a.nai_cvg_cd = 'MA'
and a.nai_svc_loc = 'Site'
group by 1,2,3,4,5,6,7,8,9,10,11
union
sel
d.acctg_yr,
d.acctg_qtr,
p.cn_st_prov as State,
case when p.cn_st_prov in ('DC','IL','IN','KS','KY','MD','MO','OH','OK','VA',' WV') then 'Central' when p.cn_st_prov in ('IA','MI','MN','MT','ND','NE','SD','WI','WY') then 'NorthCentral' when p.cn_st_prov in ('CT','DE','MA','ME','NH','NJ','NY','PA','RI','VT') then 'NorthEast' when p.cn_st_prov in ('ID','OR','WA') then 'NorthWest' when p.cn_st_prov in ('AL','AR','FL','GA','LA','MS','NC','SC','TN','TX') then 'Southeast' when p.cn_st_prov in ('AZ','CO','NM','NV','UT') then 'SouthWest' when p.cn_st_prov in ('AK','CA','HI','PR') then p.cn_st_prov else 'Other' end as StateGroup,
case when p.psv_div_ogp_no in ('006','022','026','046','057','071') then p.psv_div_ogp_no else '099' end as Division,
i.mod_platform as ProductType,
case when p.svc_prd_pln_cd in ('MPA','RPA','BPA','SSA') then p.svc_prd_pln_cd else 'Other' end as PlanType,
p.sell_org as SForAM,
cast (sum (0) as CHAR(5)) as ItemNum,
cast (sum (0) as VARCHAR(12)) as ModNum,
case when p.led_typ_cd in ('RDC','RENM','RENS','CIAQ','TECH','MA2','AMA','TSRV' ,'R1S','RENC','UPS','MA3','PE','IW','CI','MA4','NU',' TECR','REPL','R1M','TCAR','INAC','CISV','CIAE','S',' OTC','CSAT','KMRT','SAVE','DLRV') then p.led_typ_cd else 'Other' end as Lead,
sum(0) as recalls_30,
sum(0) as recalls_90,
sum(0) as RepairTime,
sum(0) as TravelTime,
sum(0) as CallsWithReplacements,
sum(0) as TotalParts,
sum(0) as CallsWithParts,
sum(0) as TotalTrips,
sum(0) as RepairCalls,
sum(0) as PMCheckCalls,
sum(0) as TotalCalls,
sum(0) as PartsCost,
sum(0) as Unrepairables,
sum(0) as FoodLoss,
sum(0) as TotalCostsExclComm,
sum(0) as RepairCostsExclCommissions,
sum(0) as PMCheckCostsExclCommissions,
sum(0) as FullyLoadedCosts,
sum(1) as InForce,
sum(case when startdate.acctg_yr_mth = d.acctg_yr_mth or enddate.acctg_yr_mth = d.acctg_yr_mth then 0.5/12.0 else 1.0/12.0 end) as EarnedUnit,
sum(case when p.svc_prd_pln_cd = 'SSA' and startdate.acctg_yr_mth = d.acctg_yr_mth then p.net_sls_am * 0.7 else 0 end + (case when startdate.acctg_yr_mth = d.acctg_yr_mth or enddate.acctg_yr_mth = d.acctg_yr_mth then 0.5 else 1.0 end) * (case when p.svc_prd_pln_cd = 'SSA' then 0.3 else 1 end) * p.net_sls_am / (case when p.mth_cvr_no_qt = 0 then 1 else p.mth_cvr_no_qt end)) as EarnedPremium
from hs_perm_tbls.seth_sold_pa3 p
join hs_dw_views.npmatfiscaldt startdate on p.cur_sta_dt = startdate.acctg_dt
join hs_dw_views.npmatfiscaldt enddate on p.cur_epr_dt = enddate.acctg_dt
join (sel distinct acctg_mth,acctg_yr_mth,acctg_qtr,acctg_yr from hs_dw_views.npmatfiscaldt where acctg_yr between 2013 and 2015) d on d.acctg_yr_mth between startdate.acctg_yr_mth and enddate.acctg_yr_mth
left join lci_dw_views.npmatsm_cus_svc_mds m on p.hs_cus_no = m.hs_cus_no and p.itm_suf_no = m.itm_suf_no
left join hs_perm_tbls.EngSvcs_prs_item_info_stg i on coalesce(m.prd_itm_no,p.prd_itm_no) = i.item and coalesce(m.psv_div_ogp_no,p.psv_div_ogp_no) = (i.div-600)
where d.acctg_yr_mth between 201301 and 201511
and p.svc_loc_cd = 'Site'
group by 1,2,3,4,5,6,7,8,9,10,11;

Tags:
cmedved 24 posts Joined 02/14
09 Mar 2016

Hmm... I didn't re-create the DDL so I could test, but I think the fix is fairly simple - I don't think it has anything to do with the union. You can run the two queries separately to confirm. In the second union, you were on the right track. However, you should not sum them, as they are part of your grouping clause.
You can turn this:

cast (sum (0) as CHAR(5)) as ItemNum,
cast (sum (0) as VARCHAR(12)) as ModNum,

Into this:

'' as ItemNum,
'' as ModNum,

 
Or, you could make them 0s, if you want. Teradata should try to implicity cast them to the data types in the first query. Basically, get rid of the sum and you should be fine.

You must sign in to leave a comment.