CREATE TABLE uco_svf.gw_T_PARTCPNT_DIM_new AS (select partcpnt_key,
partcpnt_dim_eff_dtm,
univl_cust_id,
univl_plan_id,
dc_plan_num,
dc_sub_plan_num,
univl_plan_nm,
dc_univl_plan_grpng_id,
dc_univl_plan_grpng_nm,
case 
when dc_univl_plan_grpng_eff_dt is null then ''
when dc_univl_plan_grpng_eff_dt ='' then ''
else concat(substr(dc_univl_plan_grpng_eff_dt,1,4), '-', substr(dc_univl_plan_grpng_eff_dt,5,2), '-', substr(dc_univl_plan_grpng_eff_dt,7) ) end as dc_univl_plan_grpng_eff_dt ,
univl_organztn_id,
organztn_nm,
ssn,
dc_partcpnt_ty_cd,
dc_partcpnt_ty_desc,
dc_partcpnt_stat_cd,
dc_partcpnt_extd_stat_cd,
dc_partcpnt_stat_desc,
dc_partcpnt_extd_stat_desc,
case 
when ee_hire_dt is null then ''
when ee_hire_dt ='' then ''
else concat(substr(ee_hire_dt,1,4), '-', substr(ee_hire_dt,5,2), '-', substr(ee_hire_dt,7) ) end as ee_hire_dt ,
case 
when ee_rehire_dt is null then ''
when ee_rehire_dt ='' then ''
else concat(substr(ee_rehire_dt,1,4), '-', substr(ee_rehire_dt,5,2), '-', substr(ee_rehire_dt,7) ) end as ee_rehire_dt ,
case 
when dc_alt_vestg_dt is null then ''
when dc_alt_vestg_dt ='' then ''
else concat(substr(dc_alt_vestg_dt,1,4), '-', substr(dc_alt_vestg_dt,5,2), '-', substr(dc_alt_vestg_dt,7) ) end as dc_alt_vestg_dt ,
dc_pseudo_enrld_ind,
case 
when dc_pseudo_enrld_dt is null then ''
when dc_pseudo_enrld_dt ='' then ''
else concat(substr(dc_pseudo_enrld_dt,1,4), '-', substr(dc_pseudo_enrld_dt,5,2), '-', substr(dc_pseudo_enrld_dt,7) ) end as dc_pseudo_enrld_dt ,
case 
when dc_partcpnt_enrlmt_dt is null then ''
when dc_partcpnt_enrlmt_dt ='' then ''
else concat(substr(dc_partcpnt_enrlmt_dt,1,4), '-', substr(dc_partcpnt_enrlmt_dt,5,2), '-', substr(dc_partcpnt_enrlmt_dt,7) ) end as dc_partcpnt_enrlmt_dt ,
case 
when dc_enrlmt_elgbl_dt is null then ''
when dc_enrlmt_elgbl_dt ='' then ''
else concat(substr(dc_enrlmt_elgbl_dt,1,4), '-', substr(dc_enrlmt_elgbl_dt,5,2), '-', substr(dc_enrlmt_elgbl_dt,7) ) end as dc_enrlmt_elgbl_dt ,
case 
when ee_termn_dt is null then ''
when ee_termn_dt ='' then ''
else concat(substr(ee_termn_dt,1,4), '-', substr(ee_termn_dt,5,2), '-', substr(ee_termn_dt,7) ) end as ee_termn_dt ,
ee_termn_rsn_cd,
ee_termn_rsn_desc,
partcpnt_last_nm,
partcpnt_frst_nm,
partcpnt_mid_init,
partcpnt_pfx_nm,
partcpnt_sfx_nm,
home_frst_st_addr_ln,
home_sec_st_addr_ln,
home_thrd_st_addr_ln,
home_frth_st_addr_ln,
home_city_nm,
home_ste_cd,
home_zip_cd,
zip_cd_sfx,
dc_benfy_cd,
dc_benfy_desc,
dc_reld_partcpnt_id,
dc_acct_frozen_ind,
gender_cd,
case 
when brth_dt is null then ''
when brth_dt ='' then ''
else concat(substr(brth_dt,1,4), '-', substr(brth_dt,5,2), '-', substr(brth_dt,7) ) end as brth_dt ,
case 
when dth_dt is null then ''
when dth_dt ='' then ''
else concat(substr(dth_dt,1,4), '-', substr(dth_dt,5,2), '-', substr(dth_dt,7) ) end as dth_dt ,
mrtl_stat_cd,
mrtl_stat_desc,
insider_trader_cd,
insider_trader_desc,
hce_ind,
dc_self_dird_ind,
pyrl_freq_cd,
pyrl_freq_desc,
dc_goalmkr_enrld_ind,
case 
when dc_goalmkr_enrld_dt is null then ''
when dc_goalmkr_enrld_dt ='' then ''
else concat(substr(dc_goalmkr_enrld_dt,1,4), '-', substr(dc_goalmkr_enrld_dt,5,2), '-', substr(dc_goalmkr_enrld_dt,7) ) end as dc_goalmkr_enrld_dt ,
dc_goalmkr_portf_id,
dc_autorebal_enrld_ind,
dc_taft_hrtly_educatn_ind,
rso_acct_id,
case 
when rcd_eff_dt is null then ''
when rcd_eff_dt ='' then ''
else concat(substr(rcd_eff_dt,1,4), '-', substr(rcd_eff_dt,5,2), '-', substr(rcd_eff_dt,7) ) end as rcd_eff_dt ,
case 
when rcd_end_dt is null then ''
when rcd_end_dt ='' then ''
else concat(substr(rcd_end_dt,1,4), '-', substr(rcd_end_dt,5,2), '-', substr(rcd_end_dt,7) ) end as rcd_end_dt ,
actv_rcd_ind,
bad_addr_ind,
divsion_sub_id,
rcd_del_ind,
hrs_of_svc_amt,
per_of_svc_amt,
dc_partcpnt_acct_ty_cd,
case 
when partcpnt_init_dt is null then ''
when partcpnt_init_dt ='' then ''
else concat(substr(partcpnt_init_dt,1,4), '-', substr(partcpnt_init_dt,5,2), '-', substr(partcpnt_init_dt,7) ) end as partcpnt_init_dt ,
partcpnt_law_enfrcment_ind,
partcpnt_sherriff_ind,
partcpnt_dso_ind,
dc_reld_partcpnt_ssn,
case 
when orig_auto_enrlmt_dt is null then ''
when orig_auto_enrlmt_dt ='' then ''
else concat(substr(orig_auto_enrlmt_dt,1,4), '-', substr(orig_auto_enrlmt_dt,5,2), '-', substr(orig_auto_enrlmt_dt,7) ) end as orig_auto_enrlmt_dt ,
case 
when auto_enrlmt_chng_dt is null then ''
when auto_enrlmt_chng_dt ='' then ''
else concat(substr(auto_enrlmt_chng_dt,1,4), '-', substr(auto_enrlmt_chng_dt,5,2), '-', substr(auto_enrlmt_chng_dt,7) ) end as auto_enrlmt_chng_dt ,
auto_enrlmt_chng_ind,
case 
when auto_enrlmt_decln_dt is null then ''
when auto_enrlmt_decln_dt ='' then ''
else concat(substr(auto_enrlmt_decln_dt,1,4), '-', substr(auto_enrlmt_decln_dt,5,2), '-', substr(auto_enrlmt_decln_dt,7) ) end as auto_enrlmt_decln_dt ,
cntrbn_escltn_elect_cd,
cntrbn_escltn_incrmt_pct,
case 
when cntrbn_escltn_orig_eff_dt is null then ''
when cntrbn_escltn_orig_eff_dt ='' then ''
else concat(substr(cntrbn_escltn_orig_eff_dt,1,4), '-', substr(cntrbn_escltn_orig_eff_dt,5,2),'-', substr(cntrbn_escltn_orig_eff_dt,7) ) end as cntrbn_escltn_orig_eff_dt ,
case 
when cntrbn_escltn_chng_eff_dt is null then ''
when cntrbn_escltn_chng_eff_dt ='' then ''
else concat(substr(cntrbn_escltn_chng_eff_dt,1,4), '-', substr(cntrbn_escltn_chng_eff_dt,5,2), '-', substr(cntrbn_escltn_chng_eff_dt,7) ) end as cntrbn_escltn_chng_eff_dt ,
case 
when cntrbn_escltn_opt_out_dt is null then ''
when cntrbn_escltn_opt_out_dt ='' then ''
else concat(substr(cntrbn_escltn_opt_out_dt,1,4), '-', substr(cntrbn_escltn_opt_out_dt,5,2), '-', substr(cntrbn_escltn_opt_out_dt,7) ) end as cntrbn_escltn_opt_out_dt ,
cntrbn_escltn_ctr_cnt,
cntrbn_escltn_incrmt_amt,
cntrbn_escltn_incrmt_mmdd,
cntrbn_escltn_elect_src_cd,
last_acty_oper_id,
last_acty_dtm,
sdb_acct_num,
case 
when if_frst_dollr_recvd_dt is null then ''
when if_frst_dollr_recvd_dt ='' then ''
else concat(substr(if_frst_dollr_recvd_dt,1,4), '-', substr(if_frst_dollr_recvd_dt,5,2), '-', substr(if_frst_dollr_recvd_dt,7) ) end as if_frst_dollr_recvd_dt ,
case 
when orig_if_frst_dollr_recvd_dt is null then ''
when orig_if_frst_dollr_recvd_dt ='' then ''
else concat(substr(orig_if_frst_dollr_recvd_dt,1,4), '-', substr(orig_if_frst_dollr_recvd_dt,5,2), '-', substr(orig_if_frst_dollr_recvd_dt,7) ) end as orig_if_frst_dollr_recvd_dt ,
case 
when upd_dt is null then ''
when upd_dt ='' then ''
else concat(substr(upd_dt,1,4), '-', substr(upd_dt,5,2), '-', substr(upd_dt,7) ) end as upd_dt ,
age_adj_ind,
home_ph_num,
min_distrib_suprss_opt_cd,
min_distrib_beg_dt_opt_cd,
case 
when min_distrib_elgbl_dt is null then ''
when min_distrib_elgbl_dt ='' then ''
else concat(substr(min_distrib_elgbl_dt,1,4), '-', substr(min_distrib_elgbl_dt,5,2), '-', substr(min_distrib_elgbl_dt,7) ) end as min_distrib_elgbl_dt ,
dec_sales_lead_stat_cd,
elected_defrl_rt_ind,
forced_act_ind,
case 
when forced_act_dt is null then ''
when forced_act_dt ='' then ''
else concat(substr(forced_act_dt,1,4), '-', substr(forced_act_dt,5,2), '-', substr(forced_act_dt,7) ) end as forced_act_dt ,
type_ind,
partl_goalmkr_pct,
ppa_deflt_invst_ind,
case 
when pyrl_suspn_dt is null then ''
when pyrl_suspn_dt ='' then ''
else concat(substr(pyrl_suspn_dt,1,4), '-', substr(pyrl_suspn_dt,5,2), '-', substr(pyrl_suspn_dt,7) ) end as pyrl_suspn_dt ,
pyrl_suspn_mth_num,
case 
when pyrl_suspn_end_dt is null then ''
when pyrl_suspn_end_dt ='' then ''
else concat(substr(pyrl_suspn_end_dt,1,4), '-', substr(pyrl_suspn_end_dt,5,2), '-', substr(pyrl_suspn_end_dt,7) ) end as pyrl_suspn_end_dt  ,
ee_ty_cd,
ee_ty_desc,
stmt_ty_cd,
ownr_pct_ind,
union_stat_cd,
persn_acct_anchor_id,
xfer_merge_cls_ind,
edw_acty_rsn_cd,
edw_acty_rsn_desc,
partcpnt_ph_cd,
mrngstar_opt_in_ind,
case 
when mrngstar_opt_in_set_dt is null then ''
when mrngstar_opt_in_set_dt ='' then ''
else concat(substr(mrngstar_opt_in_set_dt,1,4), '-', substr(mrngstar_opt_in_set_dt,5,2), '-', substr(mrngstar_opt_in_set_dt,7) ) end as mrngstar_opt_in_set_dt ,
case 
when anchor_min_rcd_eff_dt is null then ''
when anchor_min_rcd_eff_dt ='' then ''
else concat(substr(anchor_min_rcd_eff_dt,1,4), '-', substr(anchor_min_rcd_eff_dt,5,2), '-', substr(anchor_min_rcd_eff_dt,7) ) end as anchor_min_rcd_eff_dt ,
gender_desc,
hce_desc,
dc_taft_hrtly_educatn_desc,
dc_partcpnt_acct_ty_desc,
dc_part_stat_cat_cd,
dc_part_stat_cat_desc,
partcpnt_pri_stat_cd,
case 
when partcpnt_pri_stat_dt is null then ''
when partcpnt_pri_stat_dt ='' then ''
else concat(substr(partcpnt_pri_stat_dt,1,4), '-', substr(partcpnt_pri_stat_dt,5,2), '-', substr(partcpnt_pri_stat_dt,7) ) end as partcpnt_pri_stat_dt ,
home_ste_cd_desc,
rptng_ssn,
auto_enrlmt_chng_cd,
concat(substr(createdate,1,2), '-', substr(createdate,3,2), '-', substr(createdate,5) ) as createdate
from uco_svf.T_PARTCPNT_DIM)