 update thin_need_to_map set 
 thin_name = regexp_replace (thin_name, 'globuli$','granules' )
 ;
+update thin_need_to_map set 
+thin_name = regexp_replace (thin_name, 'sach$','sachet' )
+;
 commit
 ;
 --select * from thin_need_to_map

select  CONCEPT_CODE,INGREDIENT_CONCEPT_CODE,AMOUNT_VALUE,AMOUNT_UNIT,NUMERATOR_VALUE,NUMERATOR_UNIT,DENOMINATOR_VALUE,DENOMINATOR_UNIT from ds_all
;
--somewhere we don't have a number due to wrong parsing
select * from ds_all
;
-- update denominator with existing value for concepts having empty and non-emty denominator value/unit

;

select distinct i.DOSAGE,i.DRUG_COMP,i.THIN_CODE,i.THIN_NAME,i.GEMSCRIPT_CODE,i.GEMSCRIPT_NAME,i.VOLUME
, b.concept_id as target_id, b.concept_name as target_name, b.vocabulary_id as target_vocab, b.concept_class_id as target_class from 
i_map i
 join (
select concept_id_1,relationship_id, concept_id_2 from concept_relationship where invalid_reason is null union select concept_id_1,relationship_id, concept_id_2 from rel_to_conc_old
) r on i.concept_id = r.concept_id_1 and relationship_id in ('Maps to', 'Source - RxNorm eq') 
  join concept b on b.concept_id = r.concept_id_2  and b.vocabulary_id like 'RxNorm%'
;

