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/bsr_concepts_load.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: 2.4 KB
Line 
1/* bsr_concepts_load -- add BSR Specimines terms to custom_meta
2
3Copyright (c) 2012 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
7*/
8
9--  Check if we are in De-identified Server
10select upload_id from blueherondata.upload_status where 1=0;
11
12-- check for curated data
13select bsr_category from BSR_TERMS where 1=0;
14
15
16-- Enough preliminaries. Let's wipe out the concepts and start.
17delete from BLUEHERONMETADATA.custom_meta
18where c_fullname like '\i2b2\Specimens\%';
19
20
21-- Create the bsr_audit_info view
22create or replace view bsr_audit_info as
23select * from BlueHeronData.source_master
24where source_cd like 'BSR@%';
25
26create or replace view kumc_bsr_scheme as
27select * from BlueHeronMetadata.schemes
28where c_name = 'KUMC|BSR|SAMPLE_DESC';
29
30insert into BlueHeronMetadata.custom_meta (
31  c_hlevel, c_fullname, c_name, c_basecode, c_dimcode,
32  c_visualattributes,c_tooltip,
33  c_synonym_cd, c_facttablecolumn, c_tablename, c_columnname, c_columndatatype,
34  c_operator, m_applied_path,
35  update_date, import_date, sourcesystem_cd)
36select c_hlevel, concept_path, c_name, concept_code, concept_path,
37  c_visualattributes,c_tooltip,
38  norm.*,
39  sysdate as update_date, sysdate as import_date, bsr_audit_info.source_cd
40from BlueHeronMetadata.normal_concept norm, bsr_audit_info@id,
41(
42select 1 as c_hlevel
43     , '\i2b2\Specimens\' as concept_path
44     , 'Specimens' as c_name -- check with Russ?
45     , null as concept_code
46     , 'FA' as c_visualattributes
47     , 'Specimens' as c_tooltip
48     from dual
49union all
50select distinct 2 as c_hlevel
51     , '\i2b2\Specimens\' || bsr_category || '\' as concept_path
52     , bsr_category as c_name
53     , null as concept_code
54     , 'FA' as c_visualattributes
55     , bsr_category as c_tooltip
56     from bsr_terms
57union all
58select distinct 3 as c_hlevel
59     , '\i2b2\Specimens\' || bsr_category || '\' || subcategory || '\' as concept_path
60     , subcategory as c_name
61     , null as concept_code
62     , 'FA' as c_visualattributes
63     , subcategory as c_tooltip
64     from bsr_terms
65union all
66select 4 as c_hlevel
67     , '\i2b2\Specimens\' || bsr_category || '\' || subcategory || '\' || sample_desc  || '\' as concept_path
68     , sample_desc as c_name
69     , kumc_bsr_scheme.c_key || sample_desc as concept_code
70     , 'LA' as c_visualattributes
71     , sample_desc as c_tooltip
72     from bsr_terms, kumc_bsr_scheme
73);
Note: See TracBrowser for help on using the repository browser.