sql.OMOP_AOU_oracle_ddl.sql Maven / Gradle / Ivy
DROP TABLE USERS;
CREATE TABLE USERS
( ID NUMBER(38,0) NOT NULL ENABLE,
USERNAME VARCHAR2(255) NOT NULL ENABLE,
CONSTRAINT "USERS_PK" PRIMARY KEY ("ID")
);
CREATE SEQUENCE USER_SEQ START WITH 1 INCREMENT BY 1 NOCACHE NOCYCLE ORDER;
INSERT INTO USERS (ID, USERNAME) VALUES(USER_SEQ.NEXTVAL, 'allofususer');
--generate counts for each table and keep a log for each quarter's refresh
DROP SEQUENCE AOU_DATAREFRESH_SEQ;
CREATE SEQUENCE AOU_DATAREFRESH_SEQ START WITH 1 INCREMENT BY 1 CACHE 20 NOCYCLE ORDER;
DROP TABLE aou_datarefresh_log;
CREATE TABLE aou_datarefresh_log
(
aou_refresh_id NUMBER(38,0),
aou_tablename VARCHAR2(50),
aou_raw_rowcount NUMBER(38,0),
aou_proc_rowcount NUMBER(38,0),
aou_refresh_date DATE
);
DROP SEQUENCE AOU_CARESITE_SEQ;
CREATE SEQUENCE AOU_CARESITE_SEQ START WITH 1 INCREMENT BY 1 CACHE 20 NOCYCLE ORDER;
DROP TABLE care_site_temp;
CREATE TABLE care_site_temp
(
care_site_id VARCHAR(255) NULL,
care_site_name VARCHAR(255),
place_of_service_concept_id VARCHAR(255) NULL,
location_id VARCHAR(255),
care_site_source_value VARCHAR(255),
place_of_service_source_value VARCHAR(255)
);
DROP TABLE care_site_final;
CREATE TABLE care_site_final
(
aou_refresh_id NUMBER(38,0),
id NUMBER(38,0),
care_site_id VARCHAR(255) NULL,
care_site_name VARCHAR(255),
place_of_service_concept_id VARCHAR(255) NULL,
location_id VARCHAR(255),
care_site_source_value VARCHAR(50),
place_of_service_source_value VARCHAR(50)
);
DROP SEQUENCE AOU_PROVIDER_SEQ;
CREATE SEQUENCE AOU_PROVIDER_SEQ START WITH 1 INCREMENT BY 1 CACHE 20 NOCYCLE ORDER;
DROP TABLE provider_temp;
CREATE TABLE provider_temp
(
provider_id VARCHAR(255) NULL ,
provider_name VARCHAR(255) NULL , --
NPI VARCHAR(255) NULL , --
DEA VARCHAR(255) NULL , --
specialty_concept_id VARCHAR(255) NULL ,
care_site_id VARCHAR(255) NULL ,
year_of_birth VARCHAR(255) NULL ,
gender_concept_id VARCHAR(255) NULL , --
provider_source_value VARCHAR(255) NULL , --
specialty_source_value VARCHAR(255) NULL ,
specialty_source_concept_id VARCHAR(255) NULL ,
gender_source_value VARCHAR(255) NULL ,
gender_source_concept_id VARCHAR(255) NULL
);
DROP TABLE provider_final;
CREATE TABLE provider_final
(
aou_refresh_id NUMBER(38,0),
id NUMBER(38,0),
provider_id VARCHAR(255) NULL ,
provider_name VARCHAR(50) NULL ,
NPI VARCHAR(20) NULL ,
DEA VARCHAR(20) NULL ,
specialty_concept_id VARCHAR(255) NULL ,
care_site_id VARCHAR(255) NULL ,
year_of_birth VARCHAR(255) NULL ,
gender_concept_id VARCHAR(255) NULL ,
provider_source_value VARCHAR(50) NULL ,
specialty_source_value VARCHAR(50) NULL ,
specialty_source_concept_id VARCHAR(255) NULL ,
gender_source_value VARCHAR(255) NULL ,
gender_source_concept_id VARCHAR(255) NULL
);
DROP SEQUENCE AOU_VISITOCCURRENCE_SEQ;
CREATE SEQUENCE AOU_VISITOCCURRENCE_SEQ START WITH 1 INCREMENT BY 1 CACHE 20 NOCYCLE ORDER;
DROP TABLE visit_occurrence_temp;
CREATE TABLE visit_occurrence_temp
(
visit_occurrence_id VARCHAR(255) NULL ,
person_id VARCHAR(255) NULL ,
visit_concept_id VARCHAR(255) NULL ,
visit_start_date VARCHAR(255) NULL ,
visit_start_datetime VARCHAR(255) NULL ,
visit_end_date VARCHAR(255) NULL ,
visit_end_datetime VARCHAR(255) NULL ,
visit_type_concept_id VARCHAR(255) NULL ,
provider_id VARCHAR(255) NULL,
care_site_id VARCHAR(255) NULL,
visit_source_value VARCHAR(255) NULL,
visit_source_concept_id VARCHAR(255) NULL ,
admitting_source_concept_id VARCHAR(255) NULL ,
admitting_source_value VARCHAR(255) NULL ,
discharge_to_concept_id VARCHAR(255) NULL ,
discharge_to_source_value VARCHAR(255) NULL ,
preceding_visit_occurrence_id VARCHAR(255) NULL
);
DROP TABLE visit_occurrence_final;
CREATE TABLE visit_occurrence_final
(
aou_refresh_id NUMBER(38,0),
id NUMBER(38,0),
visit_occurrence_id VARCHAR(255) NULL ,
person_id VARCHAR(255) NULL ,
visit_concept_id VARCHAR(255) NULL ,
visit_start_date VARCHAR(255) NULL ,
visit_start_datetime VARCHAR(255) NULL ,
visit_end_date VARCHAR(255) NULL ,
visit_end_datetime VARCHAR(255) NULL ,
visit_type_concept_id VARCHAR(255) NULL ,
provider_id VARCHAR(255) NULL,
care_site_id VARCHAR(255) NULL,
visit_source_value VARCHAR(50) NULL,
visit_source_concept_id VARCHAR(255) NULL ,
admitting_source_concept_id VARCHAR(255) NULL ,
admitting_source_value VARCHAR(50) NULL ,
discharge_to_concept_id VARCHAR(255) NULL ,
discharge_to_source_value VARCHAR(50) NULL ,
preceding_visit_occurrence_id VARCHAR(255) NULL
);
DROP SEQUENCE AOU_PROCEDUREOCCURRENCE_SEQ;
CREATE SEQUENCE AOU_PROCEDUREOCCURRENCE_SEQ START WITH 1 INCREMENT BY 1 CACHE 20 NOCYCLE ORDER;
DROP TABLE procedure_occurrence_temp;
CREATE TABLE procedure_occurrence_temp
(
procedure_occurrence_id VARCHAR(255) NULL ,
person_id VARCHAR(255) NULL ,
procedure_concept_id VARCHAR(255) NULL ,
procedure_date VARCHAR(255) NULL ,
procedure_datetime VARCHAR(255) NULL ,
procedure_type_concept_id VARCHAR(255) NULL ,
modifier_concept_id VARCHAR(255) NULL ,
quantity VARCHAR(255) NULL ,
provider_id VARCHAR(255) NULL ,
visit_occurrence_id VARCHAR(255) NULL ,
procedure_source_value VARCHAR(255) NULL ,
code_type_source_value VARCHAR(255) NULL ,
procedure_source_concept_id VARCHAR(255) NULL ,
qualifier_source_value VARCHAR(255) NULL
);
DROP TABLE procedure_occurrence_final;
CREATE TABLE procedure_occurrence_final
(
aou_refresh_id NUMBER(38,0),
id NUMBER(38,0),
procedure_occurrence_id VARCHAR(255) NULL ,
person_id VARCHAR(255) NULL ,
procedure_concept_id VARCHAR(255) NULL ,
procedure_date VARCHAR(255) NULL ,
procedure_datetime VARCHAR(255) NULL ,
procedure_type_concept_id VARCHAR(255) NULL ,
modifier_concept_id VARCHAR(255) NULL ,
quantity VARCHAR(255) NULL ,
provider_id VARCHAR(255) NULL ,
visit_occurrence_id VARCHAR(255) NULL ,
procedure_source_value VARCHAR(50) NULL ,
code_type_source_value VARCHAR(8) NULL ,
procedure_source_concept_id VARCHAR(255) NULL ,
qualifier_source_value VARCHAR(50) NULL
);
DROP SEQUENCE AOU_CONDITIONOCCURRENCE_SEQ;
CREATE SEQUENCE AOU_CONDITIONOCCURRENCE_SEQ START WITH 1 INCREMENT BY 1 CACHE 20 NOCYCLE ORDER;
DROP TABLE condition_occurrence_temp;
CREATE TABLE condition_occurrence_temp
(
condition_occurrence_id VARCHAR(255) NULL ,
person_id VARCHAR(255) NULL ,
condition_concept_id VARCHAR(255) NULL ,
condition_start_date VARCHAR(255) NULL ,
condition_start_datetime VARCHAR(255) NULL ,
condition_end_date VARCHAR(255) NULL ,
condition_end_datetime VARCHAR(255) NULL ,
condition_type_concept_id VARCHAR(255) NULL ,
stop_reason VARCHAR(255) NULL ,
provider_id VARCHAR(255) NULL ,
visit_occurrence_id VARCHAR(255) NULL ,
condition_source_value VARCHAR(255) NULL ,
condition_source_concept_id VARCHAR(255) NULL ,
condition_status_source_value VARCHAR(255) NULL,
condition_status_concept_id VARCHAR(255) NULL ,
condition_code_source_value VARCHAR(255) NULL
);
DROP TABLE condition_occurrence_final;
CREATE TABLE condition_occurrence_final
(
aou_refresh_id NUMBER(38,0),
id NUMBER(38,0),
condition_occurrence_id VARCHAR(255) NULL ,
person_id VARCHAR(255) NULL ,
condition_concept_id VARCHAR(255) NULL ,
condition_start_date VARCHAR(255) NULL ,
condition_start_datetime VARCHAR(255) NULL ,
condition_end_date VARCHAR(255) NULL ,
condition_end_datetime VARCHAR(255) NULL ,
condition_type_concept_id VARCHAR(255) NULL ,
stop_reason VARCHAR(20) NULL ,
provider_id VARCHAR(255) NULL ,
visit_occurrence_id VARCHAR(255) NULL ,
condition_source_value VARCHAR(50) NULL ,
condition_source_concept_id VARCHAR(255) NULL ,
condition_status_source_value VARCHAR(50) NULL,
condition_status_concept_id VARCHAR(255) NULL ,
condition_code_source_value VARCHAR(50) NULL
);
DROP SEQUENCE AOU_MEASUREMENT_SEQ;
CREATE SEQUENCE AOU_MEASUREMENT_SEQ START WITH 1 INCREMENT BY 1 CACHE 20 NOCYCLE ORDER;
DROP TABLE measurement_temp;
CREATE TABLE measurement_temp
(
measurement_id VARCHAR(255) NULL ,
person_id VARCHAR(255) NULL ,
measurement_concept_id VARCHAR(255) NULL ,
measurement_date VARCHAR(255) NULL ,
measurement_datetime VARCHAR(255) NULL ,
measurement_type_concept_id VARCHAR(255) NULL ,
operator_concept_id VARCHAR(255) NULL ,
value_as_number VARCHAR(255) NULL ,
value_as_concept_id VARCHAR(255) NULL ,
unit_concept_id VARCHAR(255) NULL ,
range_low VARCHAR(255) NULL ,
range_high VARCHAR(255) NULL ,
provider_id VARCHAR(255) NULL ,
visit_occurrence_id VARCHAR(255) NULL ,
measurement_source_value VARCHAR(255) NULL , --
measurement_source_concept_id VARCHAR(255) NULL ,
unit_source_value VARCHAR(255) NULL , --
value_source_value VARCHAR(255) NULL , --
operator_concept_source_value VARCHAR(255) NULL --
);
DROP TABLE measurement_final;
CREATE TABLE measurement_final
(
aou_refresh_id NUMBER(38,0),
id NUMBER(38,0),
measurement_id VARCHAR(255) NULL ,
person_id VARCHAR(255) NULL ,
measurement_concept_id VARCHAR(255) NULL ,
measurement_date VARCHAR(255) NULL ,
measurement_datetime VARCHAR(255) NULL ,
measurement_type_concept_id VARCHAR(255) NULL ,
operator_concept_id VARCHAR(255) NULL ,
value_as_number VARCHAR(255) NULL ,
value_as_concept_id VARCHAR(255) NULL ,
unit_concept_id VARCHAR(255) NULL ,
range_low VARCHAR(255) NULL ,
range_high VARCHAR(255) NULL ,
provider_id VARCHAR(255) NULL ,
visit_occurrence_id VARCHAR(255) NULL ,
measurement_source_value VARCHAR(50) NULL ,
measurement_source_concept_id VARCHAR(255) NULL ,
unit_source_value VARCHAR(50) NULL ,
value_source_value VARCHAR(50) NULL ,
operator_concept_source_value VARCHAR(50) NULL
);
DROP SEQUENCE AOU_DRUGEXPOSURE_SEQ;
CREATE SEQUENCE AOU_DRUGEXPOSURE_SEQ START WITH 1 INCREMENT BY 1 CACHE 20 NOCYCLE ORDER;
DROP TABLE drug_exposure_temp;
CREATE TABLE drug_exposure_temp
(
drug_exposure_id VARCHAR(255) NULL ,
person_id VARCHAR(255) NULL ,
drug_concept_id VARCHAR(255) NULL ,
drug_exposure_start_date VARCHAR(255) NULL ,
drug_exposure_start_datetime VARCHAR(255) NULL ,
drug_exposure_end_date VARCHAR(255) NULL ,
drug_exposure_end_datetime VARCHAR(255) NULL ,
verbatim_end_date VARCHAR(255) NULL ,
drug_type_concept_id VARCHAR(255) NULL ,
stop_reason VARCHAR(255) NULL ,
refills VARCHAR(255) NULL ,
quantity VARCHAR(255) NULL ,
days_supply VARCHAR(255) NULL ,
sig CLOB NULL ,
route_concept_id VARCHAR(255) NULL ,
lot_number VARCHAR(255) NULL ,
provider_id VARCHAR(255) NULL ,
visit_occurrence_id VARCHAR(255) NULL ,
drug_source_value VARCHAR(255) NULL ,
drug_code_type_source_value VARCHAR(255) NULL ,
drug_source_concept_id VARCHAR(255) NULL ,
route_source_value VARCHAR(255) NULL ,
dose_unit_source_value VARCHAR(255) NULL,
duration_code VARCHAR(255) NULL
)
;
DROP TABLE drug_exposure_final;
CREATE TABLE drug_exposure_final
(
aou_refresh_id NUMBER(38,0),
id NUMBER(38,0),
drug_exposure_id VARCHAR(255) NULL ,
person_id VARCHAR(255) NULL ,
drug_concept_id VARCHAR(255) NULL ,
drug_exposure_start_date VARCHAR(255) NULL ,
drug_exposure_start_datetime VARCHAR(255) NULL ,
drug_exposure_end_date VARCHAR(255) NULL ,
drug_exposure_end_datetime VARCHAR(255) NULL ,
verbatim_end_date VARCHAR(255) NULL ,
drug_type_concept_id VARCHAR(255) NULL ,
stop_reason VARCHAR(20) NULL ,
refills VARCHAR(255) NULL ,
quantity VARCHAR(255) NULL ,
days_supply VARCHAR(255) NULL ,
sig CLOB NULL ,
route_concept_id VARCHAR(255) NULL ,
lot_number VARCHAR(50) NULL ,
provider_id VARCHAR(255) NULL ,
visit_occurrence_id VARCHAR(255) NULL ,
drug_source_value VARCHAR(50) NULL ,
drug_code_type_source_value VARCHAR(8) NULL ,
drug_source_concept_id VARCHAR(255) NULL ,
route_source_value VARCHAR(50) NULL ,
dose_unit_source_value VARCHAR(50) NULL
)
;
---------------------------------------------------------------------
DROP TABLE person_temp;
CREATE TABLE person_temp
(
person_id VARCHAR(255) NULL ,
gender_concept_id VARCHAR(255) NULL ,
year_of_birth VARCHAR(255) NULL ,
month_of_birth VARCHAR(255) NULL,
day_of_birth VARCHAR(255) NULL,
birth_datetime VARCHAR(255) NULL,
race_concept_id VARCHAR(255) NULL,
ethnicity_concept_id VARCHAR(255) NULL,
location_id VARCHAR(255) NULL,
provider_id VARCHAR(255) NULL,
care_site_id VARCHAR(255) NULL,
person_source_value VARCHAR(255) NULL,
gender_source_value VARCHAR(255) NULL,
gender_source_concept_id VARCHAR(255) NULL,
race_source_value VARCHAR(255) NULL,
race_source_concept_id VARCHAR(255) NULL,
ethnicity_source_value VARCHAR(255) NULL,
ethnicity_source_concept_id VARCHAR(255) NULL
);
DROP TABLE person_final;
CREATE TABLE person_final
(
aou_refresh_id NUMBER(38,0),
person_id VARCHAR(255) NULL ,
gender_concept_id VARCHAR(255) NULL ,
year_of_birth VARCHAR(255) NULL ,
month_of_birth VARCHAR(255) NULL,
day_of_birth VARCHAR(255) NULL,
birth_datetime VARCHAR(255) NULL,
race_concept_id VARCHAR(255) NULL,
ethnicity_concept_id VARCHAR(255) NULL,
location_id VARCHAR(255) NULL,
provider_id VARCHAR(255) NULL,
care_site_id VARCHAR(255) NULL,
person_source_value VARCHAR(255) NULL,
gender_source_value VARCHAR(50) NULL,
gender_source_concept_id VARCHAR(255) NULL,
race_source_value VARCHAR(50) NULL,
race_source_concept_id VARCHAR(255) NULL,
ethnicity_source_value VARCHAR(50) NULL,
ethnicity_source_concept_id VARCHAR(255) NULL
);
DROP TABLE name_temp;
CREATE TABLE name_temp
(
person_id VARCHAR(255) NULL ,
first_name VARCHAR(255) NULL ,
middle_name VARCHAR(255) NULL ,
last_name VARCHAR(255) NULL ,
suffix VARCHAR(255) NULL ,
prefix VARCHAR(255) NULL
);
DROP TABLE name_final;
CREATE TABLE name_final
(
aou_refresh_id NUMBER(38,0),
person_id VARCHAR(255) NULL ,
first_name VARCHAR(50) NULL ,
middle_name VARCHAR(50) NULL ,
last_name VARCHAR(50) NULL ,
suffix VARCHAR(50) NULL ,
prefix VARCHAR(50) NULL
);
DROP TABLE email_temp;
CREATE TABLE email_temp
(
person_id VARCHAR(255) NULL ,
email VARCHAR(255) NULL ,
email_type VARCHAR(255) NULL
);
DROP TABLE email_final;
CREATE TABLE email_final
(
aou_refresh_id NUMBER(38,0),
person_id VARCHAR(255) NULL ,
email VARCHAR(50) NULL ,
email_type VARCHAR(50) NULL
);
DROP TABLE phone_number_temp;
CREATE TABLE phone_number_temp
(
person_id VARCHAR(255) NULL ,
phone_number VARCHAR(255) NULL ,
phone_type VARCHAR(255) NULL
);
DROP TABLE phone_number_final;
CREATE TABLE phone_number_final
(
aou_refresh_id NUMBER(38,0),
person_id VARCHAR(255) NULL ,
phone_number VARCHAR(50) NULL ,
phone_type VARCHAR(50) NULL
);
DROP TABLE mrn_temp;
CREATE TABLE mrn_temp
(
person_id VARCHAR(255) NULL ,
health_system VARCHAR(255) NULL ,
mrn VARCHAR(255) NULL
);
DROP TABLE mrn_final;
CREATE TABLE mrn_final
(
aou_refresh_id NUMBER(38,0),
person_id VARCHAR(255) NULL ,
health_system VARCHAR(50) NULL ,
mrn VARCHAR(50) NULL
);
DROP TABLE address_temp;
CREATE TABLE address_temp
(
person_id VARCHAR(255) NULL,
location_id VARCHAR(255) NULL
);
DROP TABLE address_final;
CREATE TABLE address_final
(
aou_refresh_id NUMBER(38,0),
person_id VARCHAR(255) NULL,
location_id VARCHAR(255) NULL
);
DROP SEQUENCE AOU_LOCATION_SEQ;
CREATE SEQUENCE AOU_LOCATION_SEQ START WITH 1 INCREMENT BY 1 CACHE 20 NOCYCLE ORDER;
DROP TABLE location_temp;
CREATE TABLE location_temp
(
location_id VARCHAR(255) NULL ,
address_1 VARCHAR(255) NULL ,
address_2 VARCHAR(255) NULL ,
city VARCHAR(255) NULL ,
state VARCHAR(255) NULL ,
zip VARCHAR(255) NULL ,
county VARCHAR(255) NULL ,
location_source_value VARCHAR(255) NULL
);
DROP TABLE location_final;
CREATE TABLE location_final
(
aou_refresh_id NUMBER(38,0),
id NUMBER(38,0),
location_id VARCHAR(255) NULL ,
address_1 VARCHAR(50) NULL ,
address_2 VARCHAR(50) NULL ,
city VARCHAR(50) NULL ,
state VARCHAR(2) NULL ,
zip VARCHAR(9) NULL ,
county VARCHAR(20) NULL ,
location_source_value VARCHAR(50) NULL
);
DROP TABLE death_temp;
CREATE TABLE death_temp
(
person_id VARCHAR(255) NULL ,
death_date VARCHAR(255) NULL ,
death_datetime VARCHAR(255) NULL ,
death_type_concept_id VARCHAR(255) NULL ,
cause_concept_id VARCHAR(255) NULL ,
cause_source_value VARCHAR(255) NULL ,
cause_source_concept_id VARCHAR(255) NULL,
death_type_source_value VARCHAR(255) NULL
);
DROP TABLE death_final;
CREATE TABLE death_final
(
aou_refresh_id NUMBER(38,0),
person_id VARCHAR(255) NULL ,
death_date VARCHAR(255) NULL ,
death_datetime VARCHAR(255) NULL ,
death_type_concept_id VARCHAR(255) NULL ,
cause_concept_id VARCHAR(255) NULL ,
cause_source_value VARCHAR(50) NULL ,
cause_source_concept_id VARCHAR(255) NULL,
death_type_source_value VARCHAR(50) NULL
);
----------------------------------------------------------------------
DROP TABLE rejected_participant;
CREATE TABLE rejected_participant
(
aou_refresh_id NUMBER(38,0),
person_id VARCHAR(255) NULL ,
reject_reason VARCHAR(500) NULL
);
CREATE INDEX provider_fnl_provider_idx ON provider_final(provider_id);
CREATE INDEX caresite_fnl_caresite_idx ON care_site_final(care_site_id);
CREATE INDEX location_fnl_location_idx ON location_final(location_id);
CREATE INDEX visit_occ_visit_occ_idx ON visit_occurrence_final(visit_occurrence_id);
CREATE INDEX person_fnl_person_idx ON person_final(person_id);
© 2015 - 2025 Weber Informatics LLC | Privacy Policy