Untitled

select ps.prod_sk as upc_prod_sk,ec.prod_sk as ec_prod_sk
from ec
inner join ps
on ps.upc_nbr=cast(ec.upc as decimal(14))
where
ps.src_sys_cd='POS'  and
ps.edw_rec_end_dt=CAST('9999-12-31' AS DATE) and
ec.edw_rec_end_dt=CAST('9999-12-31' AS DATE) and
ec.src_sys_cd='EC' and
lower(ec.upc)      =upper(ec.upc)




with 
ec_pos as
(
  (select * from   where edw_rec_end_dt=CAST('9999-12-31' AS DATE) AND SRC_SYS_CD = 'EC' AND (upc not like '%X%' and upc not like '%a%')) 
UNION
  (select * from  where edw_rec_end_dt=CAST('9999-12-31' AS DATE) AND SRC_SYS_CD = 'POS' AND upc is null ))
 
select ps.prod_sk as upc_prod_sk,ec.prod_sk as ec_prod_sk 
from  ec_pos ec
inner join ec_pos ps
on ps.upc_nbr=cast(ec.upc as decimal(14)) 
where ps.src_sys_cd='POS'  
and ps.edw_rec_end_dt=CAST('9999-12-31' AS DATE) 
and ec.edw_rec_end_dt=CAST('9999-12-31' AS DATE) 
and ec.src_sys_cd='EC' 
and lower(ec.upc)      
    =upper(ec.upc)