    SET AMOUNT_UNIT = '[U]'
 WHERE AMOUNT_UNIT = '[iU]';
 
+- Create consolidated denominator unit for drugs that have soluble and solid ingredients
+-- in the same drug (if some drug-ingredient row row has amount, another - nominator)
+UPDATE ds_stage ds
+   SET numerator_value = amount_value,
+       numerator_unit = amount_unit,
+       amount_unit = NULL,
+       amount_value = NULL,
+       denominator_unit = 'mL'
+WHERE drug_concept_code IN (SELECT drug_concept_code
+                            FROM ds_Stage a
+                              JOIN ds_stage b USING (drug_concept_code)
+                            WHERE a.amount_value IS NOT NULL
+                            AND   b.numerator_value IS NOT NULL
+                            AND   b.DENOMINATOR_UNIT = 'mL')
+AND   amount_value IS NOT NULL;
+
+UPDATE ds_stage ds
+   SET numerator_value = amount_value,
+       numerator_unit = amount_unit,
+       amount_unit = NULL,
+       amount_value = NULL,
+       denominator_unit = 'mg'
+WHERE drug_concept_code IN (SELECT drug_concept_code
+                            FROM ds_Stage a
+                              JOIN ds_stage b USING (drug_concept_code)
+                            WHERE a.amount_value IS NOT NULL
+                            AND   b.numerator_value IS NOT NULL
+                            AND   b.DENOMINATOR_UNIT = 'mg')
+AND   amount_value IS NOT NULL;
+
+--update different denominator units
+MERGE INTO ds_stage ds
+USING
+(
+  SELECT DISTINCT a.DRUG_CONCEPT_CODE,
+         REGEXP_SUBSTR(concept_name,'^\d+(\.\d+)?') AS cx -- choose denominator from the Name
+  FROM ds_stage a
+    JOIN ds_stage b ON a.drug_concept_code = b.drug_concept_code
+    JOIN concept_stage c
+      ON c.concept_code = a.drug_concept_code
+     AND c.vocabulary_id = 'RxNorm Extension'
+  WHERE a.DENOMINATOR_VALUE != b.DENOMINATOR_VALUE
+) d ON (d.DRUG_CONCEPT_CODE = ds.DRUG_CONCEPT_CODE)
+WHEN MATCHED THEN UPDATE
+  SET DENOMINATOR_VALUE = cx WHERE d.DRUG_CONCEPT_CODE = ds.DRUG_CONCEPT_CODE;
+  
+COMMIT;
+
+--fix solid forms with denominator
+UPDATE ds_Stage
+   SET amount_unit = numerator_unit,
+       amount_value = numerator_value,
+       numerator_value = NULL,
+       numerator_unit = NULL,
+       denominator_value = NULL,
+       denominator_unit = NULL
+WHERE drug_concept_Code IN (SELECT a.concept_Code
+                            FROM concept a
+                              JOIN drug_strength d
+                                ON concept_id = drug_concept_id
+                               AND denominator_unit_concept_id IS NOT NULL
+                               AND REGEXP_LIKE (concept_name,'Tablet|Capsule') -- solid forms defined by their forms
+                               AND vocabulary_id = 'RxNorm Extension');
+
+							   
+							   
+-- Put percent into the numerator, not amount
+UPDATE ds_stage
+   SET numerator_unit = amount_unit,
+       NUMERATOR_VALUE = AMOUNT_VALUE,
+       AMOUNT_VALUE = NULL,
+       amount_unit = NULL
+WHERE amount_unit = '%';
+
+-- Fixes of various ill-defined drugs violating with RxNorm editorial policies 
+--!!!!
+UPDATE DS_STAGE
+   SET NUMERATOR_VALUE = 25
+WHERE DRUG_CONCEPT_CODE in ('OMOP303266','OMOP303267','OMOP303268');
+
+UPDATE DS_STAGE
+   SET NUMERATOR_VALUE = 1
+WHERE DRUG_CONCEPT_CODE in ( 'OMOP317478','OMOP317479','OMOP317480');
+
+UPDATE DS_STAGE
+   SET NUMERATOR_VALUE = 10000,
+       DENOMINATOR_UNIT = 'mL'
+WHERE DRUG_CONCEPT_CODE = ('OMOP420658' AND INGREDIENT_CONCEPT_CODE = '8536') 
+AND DRUG_CONCEPT_CODE in ('OMOP420658','OMOP420659','OMOP420660','OMOP420661');
+
+-- XXX Check this with Christian obviosly it's just a percent so I changed it to mg/ml select * from devv5.drug_strength join devv5.concept on drug_Concept_id=concept_id where denominator_unit_concept_id=45744809 and numerator_unit_Concept_id=8554 ;
+-- Change %/actuat into mg/mL
+UPDATE ds_stage
+   SET NUMERATOR_VALUE = NUMERATOR_VALUE*10,
+       NUMERATOR_UNIT = 'mg',
+       DENOMINATOR_VALUE = NULL,
+       DENOMINATOR_UNIT = 'mL'
+WHERE DENOMINATOR_UNIT = '{actuat}'
+AND   NUMERATOR_UNIT = '%';
+
+-- Manual fixes with strange % values
+UPDATE ds_stage
+   SET NUMERATOR_VALUE = 100,
+       DENOMINATOR_VALUE = NULL,
+       DENOMINATOR_UNIT = NULL
+WHERE NUMERATOR_UNIT = '%'
+AND   NUMERATOR_VALUE IN ('0.000283','0.1','35.3');
+
+							   
 -- Do all sorts of manual fixes
 -- Fentanyl buccal film
 -- AMOUNT_VALUE instead of NUMERATOR_VALUE

                                                   AND a.denominator_value != REGEXP_SUBSTR (concept_name,'\d+(\.\d+)?'));

COMMIT;

-- Put percent into the numerator, not amount
UPDATE ds_stage
   SET numerator_unit = amount_unit,
       NUMERATOR_VALUE = AMOUNT_VALUE,
       AMOUNT_VALUE = NULL,
       amount_unit = NULL
WHERE amount_unit = '%';

-- Fixes of various ill-defined drugs violating with RxNorm editorial policies 
--!!!!
UPDATE DS_STAGE
   SET NUMERATOR_VALUE = 25
WHERE DRUG_CONCEPT_CODE in ('OMOP303266','OMOP303267','OMOP303268');

UPDATE DS_STAGE
   SET NUMERATOR_VALUE = 1
WHERE DRUG_CONCEPT_CODE in ( 'OMOP317478','OMOP317479','OMOP317480');

UPDATE DS_STAGE
   SET DENOMINATOR_UNIT = 'mL'
WHERE DRUG_CONCEPT_CODE IN ('OMOP420658','OMOP420659','OMOP420660','OMOP420661');

-- XXX Check this with Christian obviosly it's just a percent so I changed it to mg/ml select * from devv5.drug_strength join devv5.concept on drug_Concept_id=concept_id where denominator_unit_concept_id=45744809 and numerator_unit_Concept_id=8554 ;
-- Change %/actuat into mg/mL
UPDATE ds_stage
   SET NUMERATOR_VALUE = NUMERATOR_VALUE*10,
       NUMERATOR_UNIT = 'mg',
       DENOMINATOR_VALUE = NULL,
       DENOMINATOR_UNIT = 'mL'
WHERE DENOMINATOR_UNIT = '{actuat}'
AND   NUMERATOR_UNIT = '%';

-- Manual fixes with strange % values
UPDATE ds_stage
   SET NUMERATOR_VALUE = 100,
       DENOMINATOR_VALUE = NULL,
       DENOMINATOR_UNIT = NULL
WHERE NUMERATOR_UNIT = '%'
AND   NUMERATOR_VALUE IN ('0.000283','0.1','35.3');

--deprecate ingredients that had died before 2 Feb 2017 (and so they dont exist in drug_concept_stage)
DELETE ds_stage

                              JOIN drug_concept_stage b ON a.drug_concept_code = b.concept_code
                            WHERE NVL(AMOUNT_UNIT,NUMERATOR_UNIT) IN ('cm','mm')
                            OR    DENOMINATOR_UNIT IN ('cm','mm'));
-

-- Delete combination drugs where denominators don't match
DELETE ds_stage
