 truncate table drug_strength_stage;
 
 insert into concept_relationship_stage (concept_code_1,concept_code_2,vocabulary_id_1,vocabulary_id_2,relationship_id,valid_start_date,valid_end_date)
-select distinct from_code, 
-first_value (c.concept_code) over (partition by from_code order by to_id),
-dc.vocabulary_id, 
-first_value (c.vocabulary_id) over (partition by from_code order by to_id),
+select distinct from_code, c.concept_code, dc.vocabulary_id, c.vocabulary_id,
 case when dc.concept_class_id in ('Ingredient','Brand Name','Suppier','Dose Form') then 'Source - RxNorm eq'
      else 'Maps to' end,
 sysdate,to_date ('20991231', 'yyyymmdd')  

   and cr.relationship_id = 'Maps to'
   and cr.vocabulary_id_1 = (select vocabulary_id from drug_concept_stage where rownum=1)
   and cr.vocabulary_id_2 = 'RxNorm Extension'
 where concept_code_1 not in (select from_code from map_drug);
 
 insert into map_drug (from_code, to_id, map_order)
select  distinct concept_code_1, r.concept_id, '8'  -- Clinical Drug Form
from r_existing r
  join ex e
    on r.i_combo = e.ri_combo and r.df_id = e.df_id 
   and trim(r.d_combo) is null 
   and r.bn_id = 0 and r.quant_value = 0 and r.quant_unit_id = 0 and r.bs = 0 and r.mf_id=0 
   and e.concept_id like '-%'
  join concept_relationship_stage cr
    on cr.concept_code_2 = e.concept_code
   and cr.relationship_id = 'Maps to'
   and cr.vocabulary_id_1 = (select vocabulary_id from drug_concept_stage where rownum=1)
   and cr.vocabulary_id_2 = 'RxNorm Extension'
 where concept_code_1 not in (select from_code from map_drug);
 
insert into map_drug (from_code, to_id, map_order)
with e as (

from e 
join r_existing r
on r.i_combo = e.ri_combo and r.d_combo = e.rd_combo and concept_class_id = 'Clinical Drug Comp')
select distinct cr.concept_code_1, r_concept_id, '9' -- Clinical Drug Comp
from r join e using (concept_id)
  join concept_relationship_stage cr
    on cr.concept_code_2 = e.concept_code
   and cr.relationship_id = 'Maps to'
   and cr.vocabulary_id_1 = (select vocabulary_id from drug_concept_stage where rownum=1)
   and cr.vocabulary_id_2 = 'RxNorm Extension'
 where cr.concept_code_1 not in (select from_code from map_drug)
and cnt_2=cnt+1 -- take only those where components counts are equal
; 

