 
 BEGIN
    EXECUTE IMMEDIATE 'DROP VIEW COHORT_DRUGS';
+EXCEPTION
+   WHEN OTHERS THEN
+      IF SQLCODE != -942 THEN
+         RAISE;
+      END IF;
+END;
+/
+
+BEGIN
+   EXECUTE IMMEDIATE 'DROP VIEW COHORT_DRUGS_DETAIL';
 EXCEPTION
    WHEN OTHERS THEN
       IF SQLCODE != -942 THEN

COMMIT;

-- ******************************************************
-- MVIEW xx - INPATIENT_COUNT
--
CREATE OR REPLACE VIEW TRANSFORM_ENCOUNTER_PROFILE

  SELECTED_PATIENT SP
ON 
  BPP.DEIDENTIFIED_ID = SP.DEIDENTIFIED_ID
);

-- ******************************************************
CREATE OR REPLACE VIEW COHORT_OVERALL_DBPOP_GENDER AS
(
  SELECT 
        'OVERALL' AS POPULATION
      , GENDER
      , TRUNC(COUNT(GENDER) * 100 / (SELECT COUNT(*) FROM STUDY_OVERALL_POPULATION)) AS PERCENT
      , COUNT(GENDER) AS TOTAL 
  FROM 
    STUDY_OVERALL_POPULATION 
  GROUP BY 
    GENDER
  UNION
  SELECT 
        'STUDY'  as POPULATION
      , GENDER
      , TRUNC(COUNT (GENDER) * 100 / (SELECT COUNT(*) AS PERCENT FROM COHORT_PATIENT)) AS PERCENT
      , COUNT(GENDER) AS TOTAL 
  FROM 
    COHORT_PATIENT 
  GROUP BY 
    GENDER
);

-- ******************************************************
