 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

;

regexp_like ( lower (a.thin_name), lower  (' '||b.concept_name||'( |$|s|es)')) 
or regexp_like ( lower (a.thin_name), lower  (' '||regexp_replace  (b.concept_name, 'y$', 'ies') ||'( |$)')) 
)

and vocabulary_id in('RxNorm', 'dm+d','RxNorm Extension', 'AMT', 'BDPM', 'AMIS', 'Multilex', 'DPD', 'LPD_Australia') and concept_class_id in ( 'Dose Form', 'Form', 'AU Qualifier')   and invalid_reason is null
join 
(
select  c.concept_id as source_id, nvl (d.concept_name, c.concept_name) as concept_name_2, nvl (d.concept_id, c.concept_id) as concept_id_2 ,nvl (d.vocabulary_id, c.vocabulary_id) as vocabulary_id_2 from concept c
left 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 c.concept_id = r.concept_id_1 and relationship_id ='Source - RxNorm eq'
left join concept d on d.concept_id = r.concept_id_2  and d.vocabulary_id like 'RxNorm%' and d.invalid_reason is null and d.concept_class_id = 'Dose Form'
where c.vocabulary_id in('RxNorm', 'dm+d','RxNorm Extension', 'AMT', 'BDPM', 'AMIS', 'Multilex', 'DPD', 'LPD_Australia') and c.concept_class_id in ( 'Dose Form', 'Form', 'AU Qualifier')  and c.invalid_reason is null
