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/uhc_concepts_load.sql

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

Merge with demo_concepts_3800

File size: 29.5 KB
Line 
1/* uhc_concepts_load.sql -- load i2b2 concept hierarchy from source data
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
7Information on how UHC data is organized came from documents downloaded from
8https://www.uhc.edu/.  Specifically, "CDBRM_Downloads_File_Specification.xls"
9which was downloaded and provided as an ETL reference by by the KU Hospital
10Organizational Improvement team.
11*/
12--  Check if we are in Identified Server
13select upload_id from NIGHTHERONDATA.observation_fact where 1=0;
14
15-- check for metadata.
16select  C_HLEVEL, C_FULLNAME from BLUEHERONMETADATA.UHCTERMS@DEID where 1=0;
17
18-- check connection to clarity, views
19select patientID from uhc.encounter where 1=0;
20
21
22/****
23 * Audit constants for stuff from UHC
24
25 */
26create or replace view uhc_audit_info as
27select * from BlueHeronData.source_master@deid
28where source_cd like 'UHC@%';
29
30
31delete from BLUEHERONMETADATA.uhcterms@deid;
32
33
34whenever sqlerror continue;
35truncate table uhc_from_kumc;
36drop table uhc_from_kumc;
37whenever sqlerror exit; 
38
39-- New format of UHC no longer provides uniqueness for the
40-- code column in core_measurescode.  To patch concept construction
41-- we need to re-apply this invariant (#2897)
42create or replace view uhc_core_measurescode_uniq_cd as
43select * from
44(select code, description, measureid,
45        rank() over (partition by code order by measureid asc) rank
46 from UHC.core_measurescode)
47where rank = 1;
48
49
50create global temporary table uhc_from_kumc
51on commit preserve rows as
52select
53  l, con.concept_path, con.name_char, con.concept_cd, con.concept_path c_dimcode, c_visualattributes,
54  norm.*,
55  sysdate update_date, sysdate import_date, aud.source_cd
56from (
57  select 1 as l
58        , '\i2b2\UHC\' as concept_path
59        ,null as concept_cd
60        , 'University HealthSystem Consortium' as name_char -- #997
61        , 'FA' as c_visualattributes --FA for folders
62  from dual
63  union all
64  select 2 as l
65        , '\i2b2\UHC\Demographics\' as concept_path
66        ,null as concept_cd
67        , 'UHC Demographics' as name_char
68        , 'FA' as c_visualattributes --FA for folders
69  from dual
70  union all
71  select 2 as l
72        , '\i2b2\UHC\Visit Details\' as concept_path
73        ,null as concept_cd
74        , 'UHC Visit Details' as name_char
75        , 'FA' as c_visualattributes --FA for folders
76  from dual
77  union all
78  select 2 as l
79        , '\i2b2\UHC\Core Measures\' as concept_path
80        ,null as concept_cd
81        , 'UHC Core Measures' as name_char
82        , 'FA' as c_visualattributes --FA for folders
83  from dual
84  union all
85  select 2 as l
86        , '\i2b2\UHC\Diagnosis\' as concept_path
87        ,null as concept_cd
88        , 'UHC Diagnosis' as name_char
89        , 'FA' as c_visualattributes --FA for folders
90  from dual
91  union all
92  select 2 as l
93        , '\i2b2\UHC\Procedures\' as concept_path
94        ,null as concept_cd
95        , 'UHC Procedures' as name_char
96        , 'FA' as c_visualattributes --FA for folders
97  from dual
98  union all
99  select 2 as l
100        , '\i2b2\UHC\AHRQ\' as concept_path
101        ,null as concept_cd
102        , 'UHC Agency for Healthcare Research and Quality ' as name_char
103        , 'FA' as c_visualattributes --FA for folders
104  from dual
105  union all
106  /* select 3 as l
107        , '\i2b2\UHC\Demographics\Age\' as concept_path
108        ,null as concept_cd
109        , 'Age' as name_char
110        , 'FA' as c_visualattributes --FA for folders
111  from dual
112  union all*/
113   select 3 as l
114        , '\i2b2\UHC\Demographics\Gender\' as concept_path
115        ,null as concept_cd
116        , 'Gender' as name_char
117        , 'FA' as c_visualattributes --FA for folders
118  from dual
119   union all
120   select 3 as l
121        , '\i2b2\UHC\Demographics\Race\' as concept_path
122        ,null as concept_cd
123        , 'Race' as name_char
124        , 'FA' as c_visualattributes --FA for folders
125  from dual
126  union all
127   select 3 as l
128        , '\i2b2\UHC\Diagnosis\Service Line\' as concept_path
129        ,null as concept_cd
130        , 'Service Line' as name_char
131        , 'FA' as c_visualattributes --FA for folders
132  from dual
133  union all
134   select 3 as l
135        , '\i2b2\UHC\Diagnosis\Base MS DRG\' as concept_path
136        ,null as concept_cd
137        , 'Base MS DRG' as name_char
138        , 'FA' as c_visualattributes --FA for folders
139  from dual
140  union all
141   select 3 as l
142        , '\i2b2\UHC\Diagnosis\APR-DRGs\' as concept_path
143        ,null as concept_cd
144        , 'All Patient Refined DRGs' as name_char
145        , 'FA' as c_visualattributes --FA for folders
146  from dual
147  union all
148   select 3 as l
149        , '\i2b2\UHC\Diagnosis\APR-DRG Risk of Mortality\' as concept_path
150        ,null as concept_cd
151        , 'APR-DRG Risk of Mortality' as name_char
152        , 'FA' as c_visualattributes --FA for folders
153  from dual
154  union all
155   select 3 as l
156        , '\i2b2\UHC\Diagnosis\APR-DRG Severity of Illness\' as concept_path
157        ,null as concept_cd
158        , 'APR-DRG Severity of Illness' as name_char
159        , 'FA' as c_visualattributes --FA for folders
160  from dual
161  union all
162   select 3 as l
163        , '\i2b2\UHC\Diagnosis\APR-DRG Product Line\' as concept_path
164        ,null as concept_cd
165        , 'APR-DRG Product Line' as name_char
166        , 'FA' as c_visualattributes --FA for folders
167  from dual
168  union all
169   select 3 as l
170        , '\i2b2\UHC\Diagnosis\Major Diagnostic Category\' as concept_path
171        ,null as concept_cd
172        , 'Major Diagnostic Category' as name_char
173        , 'FA' as c_visualattributes --FA for folders
174  from dual
175  union all
176   select 3 as l
177        , '\i2b2\UHC\Diagnosis\UHC MS-DRG\' as concept_path
178        ,null as concept_cd
179        , 'UHC MS-DRG' as name_char
180        , 'FA' as c_visualattributes --FA for folders
181  from dual
182  union all
183   select 3 as l
184        , '\i2b2\UHC\Diagnosis\Risk Pool\' as concept_path
185        ,null as concept_cd
186        , 'Risk Pool' as name_char
187        , 'FA' as c_visualattributes --FA for folders
188  from dual
189   union all
190   select 3 as l
191        , '\i2b2\UHC\Diagnosis\Comorbidity\' as concept_path
192        ,null as concept_cd
193        , 'Comorbidity' as name_char
194        , 'FA' as c_visualattributes --FA for folders
195  from dual
196   union all
197   select 3 as l
198        , '\i2b2\UHC\Diagnosis\Complication\' as concept_path
199        ,null as concept_cd
200        , 'Complication' as name_char
201        , 'FA' as c_visualattributes --FA for folders
202  from dual
203  union all
204   select 3 as l
205        , '\i2b2\UHC\Diagnosis\CCS ICD9 Diagnosis\' as concept_path
206        ,null as concept_cd
207        , 'CCS ICD9 Diagnosis' as name_char
208        , 'FA' as c_visualattributes --FA for folders
209  from dual
210  union all
211  select 3 as l
212        , '\i2b2\UHC\Visit Details\Length of Stay\' as concept_path
213        ,null as concept_cd
214        , 'Length of Stay' as name_char
215        , 'FA' as c_visualattributes --FA for folders
216  from dual
217  union all
218  select 3 as l
219        , '\i2b2\UHC\Visit Details\Admission Status\' as concept_path
220        ,null as concept_cd
221        , 'Admission Status' as name_char
222        , 'FA' as c_visualattributes --FA for folders
223  from dual
224  union all
225  select 3 as l
226        , '\i2b2\UHC\Visit Details\Primary Payer\' as concept_path
227        ,null as concept_cd
228        , 'Primary Payer' as name_char
229        , 'FA' as c_visualattributes --FA for folders
230  from dual
231  union all
232  select 3 as l
233        , '\i2b2\UHC\Visit Details\Secondary Payer\' as concept_path
234        ,null as concept_cd
235        , 'Secondary Payer' as name_char
236        , 'FA' as c_visualattributes --FA for folders
237  from dual
238  union all
239  select 3 as l
240        , '\i2b2\UHC\Visit Details\Discharge Status\' as concept_path
241        ,null as concept_cd
242        , 'Discharge Status' as name_char
243        , 'FA' as c_visualattributes --FA for folders
244  from dual
245  union all
246  select 3 as l
247        , '\i2b2\UHC\Visit Details\Admission Day of Week\' as concept_path
248        ,null as concept_cd
249        , 'Admission Day of Week' as name_char
250        , 'FA' as c_visualattributes --FA for folders
251  from dual
252  union all
253  select 3 as l
254        , '\i2b2\UHC\Visit Details\Physician Specialty\' as concept_path
255        ,null as concept_cd
256        , 'Physician Specialty' as name_char
257        , 'FA' as c_visualattributes --FA for folders
258  from dual
259  union all
260  select 3 as l
261        , '\i2b2\UHC\Visit Details\Physician Role\' as concept_path
262        ,null as concept_cd
263        , 'Physician Role' as name_char
264        , 'FA' as c_visualattributes --FA for folders
265  from dual
266  union all
267  select 3 as l
268        , '\i2b2\UHC\Visit Details\Admission Point Of Origin\' as concept_path
269        ,null as concept_cd
270        , 'Admission Point Of Origin' as name_char
271        , 'FA' as c_visualattributes --FA for folders
272  from dual
273  union all
274  select 3 as l
275        , '\i2b2\UHC\Visit Details\Days to Readmit\' as concept_path
276        ,'UHC|READMISSION:1' as concept_cd
277        , 'Days to Readmit' as name_char
278        , 'LA' as c_visualattributes --FA for folders
279  from dual
280  union all
281  select 3 as l
282        , '\i2b2\UHC\AHRQ\AHRQ Pediatric\' as concept_path
283        ,null as concept_cd
284        , 'AHRQ Pediatric' as name_char
285        , 'FA' as c_visualattributes --FA for folders
286  from dual
287  union all
288  select 3 as l
289        , '\i2b2\UHC\AHRQ\AHRQ Quality\' as concept_path
290        ,null as concept_cd
291        , 'AHRQ Quality' as name_char
292        , 'FA' as c_visualattributes --FA for folders
293  from dual
294  union all
295  select 3 as l
296        , '\i2b2\UHC\AHRQ\AHRQ Safety\' as concept_path
297        ,null as concept_cd
298        , 'AHRQ Safety' as name_char
299        , 'FA' as c_visualattributes --FA for folders
300  from dual
301  union all
302   select 3 as l
303        , '\i2b2\UHC\Procedures\CCS ICD9-CM Procedures\' as concept_path
304        ,null as concept_cd
305        , 'CCS ICD9-CM Procedures' as name_char
306        , 'FA' as c_visualattributes --FA for folders
307  from dual
308  union all
309  select 3 as l
310        , '\i2b2\UHC\Core Measures\' || cm.code || '\'  as concept_path
311        ,null as concept_cd
312        , cm.description as name_char
313        , 'FA' as c_visualattributes --FA for folders
314  from uhc_core_measurescode_uniq_cd cm
315  union all
316 /* select 4 as l
317        , '\i2b2\UHC\Demographics\Age\Age on Admit in Years\' as concept_path
318        ,'UHC|AGEINYRSONNADM:1' as concept_cd
319        , 'Age on Admit in Years' as name_char
320        , 'LA' as c_visualattributes --LA for leaf
321  from dual
322  union all
323  select 4 as l
324        , '\i2b2\UHC\Demographics\Age\Age on Admit in Months\' as concept_path
325        ,'UHC|AGEINMONSONNADM:1' as concept_cd
326        , 'Age on Admit in Months' as name_char
327        , 'LA' as c_visualattributes --LA for leaf
328  from dual
329  union all */
330  select 4 as l
331        , '\i2b2\UHC\Demographics\Gender\' || gend.code || '\'  as concept_path
332        ,'UHC|Gender:'|| gend.code as concept_cd
333        , gend.description as name_char
334        , 'LA' as c_visualattributes --LA for leaf
335  from uhc.enc_sex gend
336  union all
337  select 4 as l
338        , '\i2b2\UHC\Demographics\Race\' || race.code || '\'  as concept_path
339        ,'UHC|Race:'|| race.code as concept_cd
340        , race.description as name_char
341        , 'LA' as c_visualattributes --LA for leaf
342  from uhc.enc_race race
343  union all
344  select 4 as l
345        , '\i2b2\UHC\Diagnosis\Service Line\' || sline.code || '\'  as concept_path
346        ,'UHC|ServiceLine:'|| sline.code as concept_cd
347        , sline.description as name_char
348        , 'LA' as c_visualattributes --LA for leaf
349  from uhc.enc_serviceline sline
350  union all
351  select 4 as l
352        , '\i2b2\UHC\Diagnosis\Base MS DRG\' || basedrg.BASEMSDRGCODE || '\' as concept_path
353        ,'UHC|BaseMSDRG:'|| basedrg.BASEMSDRGCODE as concept_cd
354        , basedrg.BASE_MSDRG_DESCRIPTION as name_char
355        , 'LA' as c_visualattributes --LA for leaf
356  from uhc.enc_basemsdrg basedrg
357  union all
358   select 4 as l
359        , '\i2b2\UHC\Diagnosis\APR-DRGs\' || aprdrg.code || '\' as concept_path
360        ,'UHC|APRDRG:'|| aprdrg.code as concept_cd
361        , aprdrg.description as name_char
362        , 'LA' as c_visualattributes --LA for leaf
363  from UHC.enc_aprdrg aprdrg
364  union all
365   select 4  as l
366        , '\i2b2\UHC\Diagnosis\APR-DRG Risk of Mortality\' || aprrom.code || '\' as concept_path
367        ,'UHC|APRROM:'|| aprrom.code as concept_cd
368        , aprrom.description as name_char
369        , 'LA' as c_visualattributes --LA for leaf
370  from UHC.enc_apr_rom aprrom
371  union all
372   select 4 as l
373        , '\i2b2\UHC\Diagnosis\APR-DRG Severity of Illness\' || aprsoi.code || '\' as concept_path
374        ,'UHC|APRSOI:'|| aprsoi.code as concept_cd
375        , aprsoi.description as name_char
376        , 'LA' as c_visualattributes --LA for leaf
377  from UHC.enc_apr_soi aprsoi
378  union all
379   select 4  as l
380        , '\i2b2\UHC\Diagnosis\APR-DRG Product Line\' || aprprdline.code || '\' as concept_path
381        ,'UHC|APRPRODLINE:'|| aprprdline.code as concept_cd
382        , aprprdline.description as name_char
383        , 'LA' as c_visualattributes --LA for leaf
384  from UHC.enc_productline aprprdline
385  union all
386   select 4  as l
387        , '\i2b2\UHC\Diagnosis\Major Diagnostic Category\' || mdc.code || '\' as concept_path
388        ,'UHC|MSDRGMDC:'|| mdc.code as concept_cd
389        , mdc.description as name_char
390        , 'LA' as c_visualattributes --LA for leaf
391  from UHC.enc_mdc mdc
392  union all
393   select 4  as l
394        , '\i2b2\UHC\Diagnosis\UHC MS-DRG\' || uhcmsdrg.code || '\' as concept_path
395        ,'UHC|UHCMSDRG:'|| uhcmsdrg.code as concept_cd
396        , uhcmsdrg.description as name_char
397        , 'LA' as c_visualattributes --LA for leaf
398  from UHC.enc_uhcmsdrg uhcmsdrg
399  union all
400   select 4  as l
401        , '\i2b2\UHC\Diagnosis\Risk Pool\' || riskpool.code || '\' as concept_path
402        ,'UHC|RISKPOOL:'|| riskpool.code as concept_cd
403        , riskpool.description as name_char
404        , 'LA' as c_visualattributes --LA for leaf
405  from UHC.riskpool_riskpoolcode riskpool
406   union all
407   select 4  as l
408        , '\i2b2\UHC\Diagnosis\CCS ICD9 Diagnosis\' || ccsdiag.code || '\' as concept_path
409        ,'UHC|CCSICD9DIAG:'|| ccsdiag.code as concept_cd
410        , ccsdiag.description as name_char
411        , 'LA' as c_visualattributes --LA for leaf
412  from UHC.diag_icd9_ccs ccsdiag
413  union all
414   select 4  as l
415        , '\i2b2\UHC\Diagnosis\Comorbidity\' || comorb.code || '\' as concept_path
416        ,'UHC|COMORBIDITY:'|| comorb.code as concept_cd
417        , comorb.description as name_char
418        , 'LA' as c_visualattributes --LA for leaf
419  from UHC.comorbidity_comorbiditycode comorb
420  union all
421   select 4  as l
422        , '\i2b2\UHC\Diagnosis\Complication\' || compl.code || '\' as concept_path
423        ,'UHC|COMPLICATION:'|| compl.code as concept_cd
424        , compl.description as name_char
425        , 'LA' as c_visualattributes --LA for leaf
426  from UHC.complication_complicationcode compl
427  union all
428  select 4 as l
429        , '\i2b2\UHC\Visit Details\Length of Stay\Hospital\' as concept_path
430        ,'UHC|LOS:1' as concept_cd
431        , 'Hospital LOS' as name_char
432        , 'LA' as c_visualattributes --LA for leaf
433  from dual
434  union all
435  select 4 as l
436        , '\i2b2\UHC\Visit Details\Length of Stay\ICU Days\' as concept_path
437        ,'UHC|ICUDAYS:1' as concept_cd
438        , 'ICU Days' as name_char
439        , 'LA' as c_visualattributes --LA for leaf
440  from dual
441  union all
442  select 4 as l
443        , '\i2b2\UHC\Visit Details\Length of Stay\Expected LOS\' as concept_path
444        , null as concept_cd
445        , 'Expected LOS' as name_char
446        , 'FA' as c_visualattributes
447  from dual
448  union all
449  select 5 as l
450        , '\i2b2\UHC\Visit Details\Length of Stay\Expected LOS\' ||
451          art.risktypecode || '\' as concept_path
452        , 'UHC|EXPECTEDLOS:' || art.risktypecode as concept_cd
453        , coalesce(rtl.risktypelabel, to_char(art.risktypecode)) as name_char
454        , 'LA' as c_visualattributes
455  from (select distinct risktypecode from uhc.expectedvalues) art
456  left join risktypelabels rtl on art.risktypecode = rtl.risktypecode
457  -- include only identified risk type calculations
458  where art.risktypecode is not null
459  union all
460  select 4 as l
461        , '\i2b2\UHC\Visit Details\Physician Specialty\' || spec.code || '\'  as concept_path
462        ,'UHC|PHYSSPECCODE:'|| spec.code as concept_cd
463        , spec.description as name_char
464        , 'LA' as c_visualattributes --LA for leaf
465  from uhc.physician_specialtycode spec
466  union all
467  select 4 as l
468        , '\i2b2\UHC\Visit Details\Secondary Payer\' || payer.code || '\'  as concept_path
469        ,'UHC|SECONDARYPAYER:'|| payer.code as concept_cd
470        , payer.description as name_char
471        , 'LA' as c_visualattributes --LA for leaf
472  from UHC.enc_uhcpay payer
473  union all
474  select 4 as l
475        , '\i2b2\UHC\Visit Details\Primary Payer\' || payer.code || '\'  as concept_path
476        ,'UHC|PRIMARYPAYER:'|| payer.code as concept_cd
477        , payer.description as name_char
478        , 'LA' as c_visualattributes --LA for leaf
479  from UHC.enc_uhcpay payer
480  union all
481  select 4 as l
482        , '\i2b2\UHC\Visit Details\Physician Role\' || spec.code || '\'  as concept_path
483        ,'UHC|PHYSROLE:'|| spec.code as concept_cd
484        , spec.description as name_char
485        , 'LA' as c_visualattributes --LA for leaf
486  from UHC.physician_otherphysicianrole spec
487  union all
488  select 4 as l
489        , '\i2b2\UHC\Visit Details\Admission Point Of Origin\' || admsrc.id || '\'  as concept_path
490        ,'UHC|ADMSRC:'|| admsrc.id as concept_cd
491        , admsrc.description as name_char
492        , 'LA' as c_visualattributes --LA for leaf
493  from UHC.enc_adm_src_po_origin admsrc
494  union all
495  select 4 as l
496        , '\i2b2\UHC\Visit Details\Admission Day of Week\Sunday\' as concept_path
497        ,'UHC|ADMDAY:SUNDAY' as concept_cd
498        , 'Sunday' as name_char
499        , 'LA' as c_visualattributes --FA for folders
500  from dual
501  union all
502  select 4 as l
503        , '\i2b2\UHC\Visit Details\Admission Day of Week\Monday\' as concept_path
504        ,'UHC|ADMDAY:MONDAY' as concept_cd
505        , 'Monday' as name_char
506        , 'LA' as c_visualattributes --FA for folders
507  from dual
508  union all
509  select 4 as l
510        , '\i2b2\UHC\Visit Details\Admission Day of Week\Tuesday\' as concept_path
511        ,'UHC|ADMDAY:TUESDAY' as concept_cd
512        , 'Tuesday' as name_char
513        , 'LA' as c_visualattributes --FA for folders
514  from dual
515  union all
516  select 4 as l
517        , '\i2b2\UHC\Visit Details\Admission Day of Week\Wednesday\' as concept_path
518        ,'UHC|ADMDAY:WEDNESDAY' as concept_cd
519        , 'Wednesday' as name_char
520        , 'LA' as c_visualattributes --FA for folders
521  from dual
522  union all
523  select 4 as l
524        , '\i2b2\UHC\Visit Details\Admission Day of Week\Thursday\' as concept_path
525        ,'UHC|ADMDAY:THURSDAY' as concept_cd
526        , 'Thursday' as name_char
527        , 'LA' as c_visualattributes --FA for folders
528  from dual
529  union all
530  select 4 as l
531        , '\i2b2\UHC\Visit Details\Admission Day of Week\Friday\' as concept_path
532        ,'UHC|ADMDAY:FRIDAY' as concept_cd
533        , 'Friday' as name_char
534        , 'LA' as c_visualattributes --FA for folders
535  from dual
536  union all
537  select 4 as l
538        , '\i2b2\UHC\Visit Details\Admission Day of Week\Saturday\' as concept_path
539        ,'UHC|ADMDAY:SATURDAY' as concept_cd
540        , 'Saturday' as name_char
541        , 'LA' as c_visualattributes --FA for folders
542  from dual
543  union all
544  select 4 as l
545        , '\i2b2\UHC\Visit Details\Discharge Status\' || disch.code || '\'  as concept_path
546        ,'UHC|DISCHSTCODE:'|| disch.code as concept_cd
547        , disch.description as name_char
548        , 'LA' as c_visualattributes --LA for leaf
549  from UHC.enc_dischargestatuscode disch
550  union all
551  select 4 as l
552        , '\i2b2\UHC\Visit Details\Admission Status\' || admtst.code || '\'  as concept_path
553        ,'UHC|ADMSTCODE:'|| admtst.code as concept_cd
554        , admtst.description as name_char
555        , 'LA' as c_visualattributes --LA for leaf
556  from UHC.enc_adm_status_code admtst
557  union all
558  select 4 as l
559        , '\i2b2\UHC\AHRQ\AHRQ Pediatric\' || ahrq.code || '\'  as concept_path
560        ,null as concept_cd
561        ,ahrq.description as name_char
562        , 'FA' as c_visualattributes --LA for leaf
563  from UHC.ahrq_pediatriccode ahrq
564  union all
565   select 4 as l
566        , '\i2b2\UHC\AHRQ\AHRQ Quality\' || ahrq.code || '\'  as concept_path
567        ,null as concept_cd
568        ,ahrq.description as name_char
569        , 'FA' as c_visualattributes --LA for leaf
570  from UHC.ahrq_qualitycode ahrq
571  union all
572    select 4 as l
573        , '\i2b2\UHC\AHRQ\AHRQ Safety\' || ahrq.code || '\'  as concept_path
574        ,null as concept_cd
575        ,ahrq.description as name_char
576        , 'FA' as c_visualattributes --LA for leaf
577  from UHC.ahrq_safetycode ahrq
578  union all
579   select 4 as l
580        , '\i2b2\UHC\Procedures\CCS ICD9-CM Procedures\'  || ccs.code || '\'  as concept_path
581        ,'UHC|CCSICD9CMPROC:'|| ccs.code as concept_cd
582        ,ccs.description as name_char
583        , 'LA' as c_visualattributes --LA for folders
584  from UHC.proc_ccs_code ccs
585  union all
586   select 5 as l
587        , '\i2b2\UHC\AHRQ\AHRQ Pediatric\' || ahrq.code || '\' || ccode.code || '\' as concept_path
588        ,'UHC|AHRQPEDIATRIC:'|| ahrq.code || '|'  || ccode.code  as concept_cd
589        , ccode.description as name_char
590        , 'LA' as c_visualattributes --LA for leaf
591  from UHC.ahrq_pediatriccode ahrq,
592      uhc.ahrq_qualifier ccode
593  union all
594   select 5 as l
595        , '\i2b2\UHC\AHRQ\AHRQ Quality\' || ahrq.code || '\' || ccode.code || '\' as concept_path
596        ,'UHC|AHRQQUALITY:'|| ahrq.code || '|'  || ccode.code  as concept_cd
597        , ccode.description as name_char
598        , 'LA' as c_visualattributes --LA for leaf
599  from UHC.ahrq_qualitycode ahrq,
600   uhc.ahrq_qualifier ccode
601  union all
602    select 5 as l
603        , '\i2b2\UHC\AHRQ\AHRQ Safety\' || ahrq.code || '\' || ccode.code || '\' as concept_path
604        ,'UHC|AHRQSAFETY:'|| ahrq.code || '|'  || ccode.code  as concept_cd
605        , ccode.description as name_char
606        , 'LA' as c_visualattributes --LA for leaf
607  from UHC.ahrq_safetycode ahrq,
608   uhc.ahrq_qualifier ccode
609  ) con
610, BlueHeronMetadata.normal_concept@deid norm, uhc_audit_info aud;
611
612/* Add core measures rows since order_by will not work with union in sql*/
613insert into uhc_from_kumc
614(l, concept_path, name_char, concept_cd, c_dimcode, c_visualattributes,
615  C_SYNONYM_CD,C_FACTTABLECOLUMN,C_TABLENAME,C_COLUMNNAME,C_COLUMNDATATYPE,C_OPERATOR,M_APPLIED_PATH,
616  update_date,import_date, source_cd)
617select 4 as l
618        , '\i2b2\UHC\Core Measures\'  || cm.code || '\'  || ccode.code || '\' as concept_path
619        , ccode.description as name_char
620        ,'UHC|COREMEASURE|'|| cm.code || ':'  || ccode.code  as concept_cd
621        ,'\i2b2\UHC\Core Measures\'  || cm.code || '\'  || ccode.code || '\' as c_dimcode
622        , 'LA' as c_visualattributes
623        ,norm.C_SYNONYM_CD
624        ,norm.C_FACTTABLECOLUMN
625        ,norm.C_TABLENAME
626        ,norm.C_COLUMNNAME
627        ,norm.C_COLUMNDATATYPE
628        ,norm.C_OPERATOR
629        ,norm.M_APPLIED_PATH
630        ,sysdate as update_date
631        ,sysdate as import_date
632        ,aud.source_cd
633  from
634 uhc_core_measurescode_uniq_cd cm,
635 UHC.core_meas_meascat_assigncode ccode,
636 BlueHeronMetadata.normal_concept@deid norm,
637 uhc_audit_info aud
638order by cm.code, ccode.code
639;
640
641insert into BLUEHERONMETADATA.uhcterms@deid (
642  c_hlevel, c_fullname, c_name, c_basecode, c_dimcode,
643  c_visualattributes,
644  c_synonym_cd, c_facttablecolumn, c_tablename, c_columnname, c_columndatatype,
645  c_operator,  m_applied_path,
646  update_date, import_date, sourcesystem_cd
647)
648select * from uhc_from_kumc
649;
650-- 386 rows inserted.
651
652
653update BLUEHERONMETADATA.uhcterms@deid
654set c_metadataxml = '<?xml version="1.0"?>
655 <ValueMetadata>
656 <Version>3.02</Version>
657 <CreationDateTime>'|| sysdate ||'</CreationDateTime>
658 <TestID>'|| 'Hospital LOS' ||'</TestID>
659 <TestName>'|| 'Hospital LOS' ||'</TestName>
660 <DataType>PosFloat</DataType>
661 <CodeType>GRP</CodeType>
662 <Loinc>Dummy2019-8@@</Loinc>
663 <Flagstouse>HL</Flagstouse>
664 <Oktousevalues>The units in this set of tests are not equivalent,
665 therefore numeric comparisons between the tests are not possible.</Oktousevalues>
666 <MaxStringLength></MaxStringLength>
667 <LowofLowValue></LowofLowValue>
668 <HighofLowValue></HighofLowValue>
669 <LowofHighValue></LowofHighValue>
670 <HighofHighValue></HighofHighValue>
671 <LowofToxicValue></LowofToxicValue>
672 <HighofToxicValue></HighofToxicValue>
673 <EnumValues></EnumValues>
674 <CommentsDeterminingExclusion>
675   <Com></Com>
676 </CommentsDeterminingExclusion>
677 <UnitValues>
678   <NormalUnits>Default</NormalUnits>
679   <ConvertingUnits>
680     <Units></Units>
681     <MultiplyingFactor></MultiplyingFactor>
682   </ConvertingUnits>
683 </UnitValues>
684 <Analysis>
685   <Enums />
686   <Counts />
687   <New />
688 </Analysis>
689</ValueMetadata>'
690where c_fullname = '\i2b2\UHC\Visit Details\Length of Stay\Hospital\';
691commit;
692
693update BLUEHERONMETADATA.uhcterms@deid
694set c_metadataxml = '<?xml version="1.0"?>
695 <ValueMetadata>
696 <Version>3.02</Version>
697 <CreationDateTime>'|| sysdate ||'</CreationDateTime>
698 <TestID>'|| 'Expected LOS' ||'</TestID>
699 <TestName>'|| 'Expected LOS' ||'</TestName>
700 <DataType>PosFloat</DataType>
701 <CodeType>GRP</CodeType>
702 <Loinc>Dummy2019-8@@</Loinc>
703 <Flagstouse>HL</Flagstouse>
704 <Oktousevalues>The units in this set of tests are not equivalent,
705 therefore numeric comparisons between the tests are not possible.</Oktousevalues>
706 <MaxStringLength></MaxStringLength>
707 <LowofLowValue></LowofLowValue>
708 <HighofLowValue></HighofLowValue>
709 <LowofHighValue></LowofHighValue>
710 <HighofHighValue></HighofHighValue>
711 <LowofToxicValue></LowofToxicValue>
712 <HighofToxicValue></HighofToxicValue>
713 <EnumValues></EnumValues>
714 <CommentsDeterminingExclusion>
715   <Com></Com>
716 </CommentsDeterminingExclusion>
717 <UnitValues>
718   <NormalUnits>Default</NormalUnits>
719   <ConvertingUnits>
720     <Units></Units>
721     <MultiplyingFactor></MultiplyingFactor>
722   </ConvertingUnits>
723 </UnitValues>
724 <Analysis>
725   <Enums />
726   <Counts />
727   <New />
728 </Analysis>
729</ValueMetadata>'
730where c_fullname like '\i2b2\UHC\Visit Details\Length of Stay\Expected LOS\%';
731commit;
732
733update BLUEHERONMETADATA.uhcterms@deid
734set c_metadataxml = '<?xml version="1.0"?>
735 <ValueMetadata>
736 <Version>3.02</Version>
737 <CreationDateTime>'|| sysdate ||'</CreationDateTime>
738 <TestID>'|| 'ICU Days' ||'</TestID>
739 <TestName>'|| 'ICU Days' ||'</TestName>
740 <DataType>PosFloat</DataType>
741 <CodeType>GRP</CodeType>
742 <Loinc>Dummy2019-8@@</Loinc>
743 <Flagstouse>HL</Flagstouse>
744 <Oktousevalues>The units in this set of tests are not equivalent,
745 therefore numeric comparisons between the tests are not possible.</Oktousevalues>
746 <MaxStringLength></MaxStringLength>
747 <LowofLowValue></LowofLowValue>
748 <HighofLowValue></HighofLowValue>
749 <LowofHighValue></LowofHighValue>
750 <HighofHighValue></HighofHighValue>
751 <LowofToxicValue></LowofToxicValue>
752 <HighofToxicValue></HighofToxicValue>
753 <EnumValues></EnumValues>
754 <CommentsDeterminingExclusion>
755   <Com></Com>
756 </CommentsDeterminingExclusion>
757 <UnitValues>
758   <NormalUnits>Default</NormalUnits>
759   <ConvertingUnits>
760     <Units></Units>
761     <MultiplyingFactor></MultiplyingFactor>
762   </ConvertingUnits>
763 </UnitValues>
764 <Analysis>
765   <Enums />
766   <Counts />
767   <New />
768 </Analysis>
769</ValueMetadata>'
770where c_fullname = '\i2b2\UHC\Visit Details\Length of Stay\ICU Days\';
771commit;
772
773
774update BLUEHERONMETADATA.uhcterms@deid
775set c_metadataxml = '<?xml version="1.0"?>
776 <ValueMetadata>
777 <Version>3.02</Version>
778 <CreationDateTime>'|| sysdate ||'</CreationDateTime>
779 <TestID>'|| 'Admission Day of Week' ||'</TestID>
780 <TestName>'|| 'Admission Day of Week' ||'</TestName>
781 <DataType>PosFloat</DataType>
782 <CodeType>GRP</CodeType>
783 <Loinc>Dummy2019-8@@</Loinc>
784 <Flagstouse>HL</Flagstouse>
785 <Oktousevalues>The units in this set of tests are not equivalent,
786 therefore numeric comparisons between the tests are not possible.</Oktousevalues>
787 <MaxStringLength></MaxStringLength>
788 <LowofLowValue></LowofLowValue>
789 <HighofLowValue></HighofLowValue>
790 <LowofHighValue></LowofHighValue>
791 <HighofHighValue></HighofHighValue>
792 <LowofToxicValue></LowofToxicValue>
793 <HighofToxicValue></HighofToxicValue>
794 <EnumValues></EnumValues>
795 <CommentsDeterminingExclusion>
796   <Com></Com>
797 </CommentsDeterminingExclusion>
798 <UnitValues>
799   <NormalUnits>Default</NormalUnits>
800   <ConvertingUnits>
801     <Units></Units>
802     <MultiplyingFactor></MultiplyingFactor>
803   </ConvertingUnits>
804 </UnitValues>
805 <Analysis>
806   <Enums />
807   <Counts />
808   <New />
809 </Analysis>
810</ValueMetadata>'
811where c_fullname = '\i2b2\UHC\Visit Details\Admission Day of Week\';
812commit;
813
814update BLUEHERONMETADATA.uhcterms@deid
815set c_metadataxml = '<?xml version="1.0"?>
816 <ValueMetadata>
817 <Version>3.02</Version>
818 <CreationDateTime>'|| sysdate ||'</CreationDateTime>
819 <TestID>'|| 'Days to Readmit' ||'</TestID>
820 <TestName>'|| 'Days to Readmit' ||'</TestName>
821 <DataType>PosFloat</DataType>
822 <CodeType>GRP</CodeType>
823 <Loinc>Dummy2019-8@@</Loinc>
824 <Flagstouse>HL</Flagstouse>
825 <Oktousevalues>The units in this set of tests are not equivalent,
826 therefore numeric comparisons between the tests are not possible.</Oktousevalues>
827 <MaxStringLength></MaxStringLength>
828 <LowofLowValue></LowofLowValue>
829 <HighofLowValue></HighofLowValue>
830 <LowofHighValue></LowofHighValue>
831 <HighofHighValue></HighofHighValue>
832 <LowofToxicValue></LowofToxicValue>
833 <HighofToxicValue></HighofToxicValue>
834 <EnumValues></EnumValues>
835 <CommentsDeterminingExclusion>
836   <Com></Com>
837 </CommentsDeterminingExclusion>
838 <UnitValues>
839   <NormalUnits>Default</NormalUnits>
840   <ConvertingUnits>
841     <Units></Units>
842     <MultiplyingFactor></MultiplyingFactor>
843   </ConvertingUnits>
844 </UnitValues>
845 <Analysis>
846   <Enums />
847   <Counts />
848   <New />
849 </Analysis>
850</ValueMetadata>'
851where c_fullname = '\i2b2\UHC\Visit Details\Days to Readmit\';
852commit;
853
854
855-- select c_hlevel, c_name from BLUEHERONMETADATA.uhcterms@deid;
856-- select count(*) from BLUEHERONMETADATA.uhcterms@deid;
857-- 386
Note: See TracBrowser for help on using the repository browser.