Note: We no longer publish the latest version of our code here. We primarily use a kumc-bmi github organization. The heron ETL repository, in particular, is not public. Peers in the informatics community should see MultiSiteDev for details on requesting access.

source: heron_load/schemes.sql @ 0:42ad7288920a

heron-michigan tip
Last change on this file since 0:42ad7288920a was 0:42ad7288920a, checked in by Matt Hoag <mhoag@…>, 6 years ago

Merge with demo_concepts_3800

File size: 3.3 KB
Line 
1/* HERON schemes used in the construction of i2b2 concept_cd's
2
3Copyright (c) 2015 University of Kansas Medical Center
4part of the HERON* open source codebase; see NOTICE file for license details.
5* http://informatics.kumc.edu/work/wiki/HERON
6
7Schemes derived from curated_data/schemes.csv
8*/
9
10create or replace view demo_religion_scheme as
11select c_key from BlueHeronMetaData.schemes where c_name like '%DEM|RELIGION';
12
13create or replace view demo_language_scheme as
14select c_key from BlueHeronMetaData.schemes where c_name like '%DEM|LANGUAGE';
15
16create or replace view demo_race_scheme as
17select c_key from BlueHeronMetaData.schemes where c_name like '%DEM|RACE';
18
19create or replace view demo_ethnicity_scheme as
20select c_key from BlueHeronMetaData.schemes where c_name like '%DEM|ETHNICITY';
21
22create or replace view demo_state_scheme as
23select c_key from BlueHeronMetaData.schemes where c_name like '%DEM|STATE';
24
25create or replace view demo_epic_email_scheme as
26select c_key from BlueHeronMetaData.schemes where c_name like '%EPIC|emailexists%';
27
28create or replace view demo_idx_email_scheme as
29select c_key from BlueHeronMetaData.schemes where c_name like '%IDX|emailexists%';
30
31create or replace view myc_status_scheme as
32select c_key from BlueHeronMetaData.schemes where c_name like '%MYC_STATUS%';
33
34create or replace view myc_recv_email_scheme as
35select c_key from BlueHeronMetaData.schemes where c_name like '%MYC_RECV_EMAIL%';
36
37create or replace view myc_accessed_scheme as
38select c_key from BlueHeronMetaData.schemes where c_name like '%MYC_ACCESSED%';
39
40create or replace view dx_id_scheme as
41select c_key from BlueHeronMetaData.schemes where c_name like '%DX_ID';
42
43create or replace view icd9_scheme as
44select c_key from BlueHeronMetaData.schemes where c_name like '%ICD9';
45
46create or replace view icd10_scheme as
47select c_key from BlueHeronMetaData.schemes where c_name like '%ICD10';
48
49create or replace view note_type_scheme as
50select c_key from BlueHeronMetaData.schemes where c_name like '%REPORTS|NOTETYPES';
51
52create or replace view scheme_labels as
53select demo_religion_scheme.c_key as demo_religion_scheme,
54       demo_language_scheme.c_key as demo_language_scheme,
55       demo_race_scheme.c_key as demo_race_scheme,
56       demo_ethnicity_scheme.c_key as demo_ethnicity_scheme,
57       demo_state_scheme.c_key as demo_state_scheme,
58       demo_epic_email_scheme.c_key as demo_epic_email_scheme,
59       demo_idx_email_scheme.c_key as demo_idx_email_scheme,
60       myc_status_scheme.c_key as myc_status_scheme,
61       myc_recv_email_scheme.c_key as myc_recv_email_scheme,
62       myc_accessed_scheme.c_key as myc_accessed_scheme,
63       dx_id_scheme.c_key as dx_id_scheme,
64       icd9_scheme.c_key as icd9_scheme,
65       icd10_scheme.c_key as icd10_scheme,
66       note_type_scheme.c_key as note_type_scheme
67from demo_religion_scheme
68cross join demo_language_scheme
69cross join demo_race_scheme
70cross join demo_ethnicity_scheme
71cross join demo_state_scheme
72cross join demo_epic_email_scheme
73cross join demo_idx_email_scheme
74cross join myc_status_scheme
75cross join myc_recv_email_scheme
76cross join myc_accessed_scheme
77cross join dx_id_scheme
78cross join icd9_scheme
79cross join icd10_scheme
80cross join note_type_scheme;
81
82/* Test to insure that the schemes are all present and accounted for and there
83   are no duplicates.
84 */
85select case when count(*) = 1 then 1 else 1/0 end schemes_labels_okay
86from scheme_labels;
Note: See TracBrowser for help on using the repository browser.