Union of tables with different columns

Database
BGT
N/A

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_ccn_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_ccn_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_ccn_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 (1)
1 REPLY
Enthusiast

Re: Union of tables with different columns

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.