 select 'FAERS' as database, caseid, cast(null as varchar) as isr, caseversion, i_f_code, event_dt, age, sex, reporter_country, primaryid, 
 drugname_list, reac_pt_list, fda_dt, null as imputed_field_name 
 from casedemo 
-union all
+union
 select 'LAERS' as database, "CASE" as caseid, isr, cast ('0' as varchar) as caseversion, i_f_cod as i_f_code, event_dt, age, gndr_cod as sex, e.country_code as reporter_country, cast("CASE" || '0' as varchar) as primaryid, 
 drugname_list, reac_pt_list, fda_dt, null as imputed_field_name 
 from casedemo_legacy a 
 left outer join country_code e
-on upper(a.reporter_country) = upper(e.country_name)
+on upper(a.reporter_country) = upper(e.country_name);
 
 ------------------------------
 

create table casedemo as
select caseid, caseversion, i_f_code, event_dt, age, sex, reporter_country, d.primaryid, drugname_list, reac_pt_list, fda_dt
from demo d
inner join drugname_list dl
on d.primaryid = dl.primaryid 
inner join reac_pt_list rpl
on d.primaryid = rpl.primaryid;

----------------------

create table casedemo_legacy as
select "CASE", i_f_cod, event_dt, age, gndr_cod, reporter_country, d.isr, drugname_list, reac_pt_list, fda_dt
from demo_legacy d
left outer join drugname_legacy_list dl
on d.isr = dl.isr 
left outer join reac_pt_legacy_list rpl
on d.isr = rpl.isr;

------------------------------
