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/epic_flowsheets_transform.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: 20.4 KB
Line 
1/** epic_flowsheets_transform.sql -- load Epic EMR flowsheet facts
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 * also includes temp work tables.
8 *
9 * See also epic_etl.py, epic_flowsheets_load.sql
10 *
11 * References:
12 *
13 * Lesson 4: Flowsheet Data
14 * Clarity Data Model - EpicCare Inpatient Spring 2008 Training Companion
15 * https://userweb.epic.com/epiclib/epicdoc/EpicWiseSpr08/Clarity/Clarity%20Training%20Companions/CLR209%20Data%20Model%20-%20EpicCare%20Inpatient/04TC%20Flowsheet%20Data.doc
16 */
17
18-- to see the time detail...
19alter session set NLS_DATE_FORMAT = 'YYYY-MM-DD HH:MI';
20
21insert into etl_test_values (test_value, test_domain, test_name, result_id, result_date)
22with test_key as (
23  select 'Flowsheets' test_domain,
24  'val_type_seen_before' test_name from dual
25  )
26, test_values as (
27  select count(*) test_value, test_key.* from (
28    select distinct zcvt.name
29    from CLARITY.ip_flwsht_meas ifm
30    join CLARITY.ip_flo_gp_data ifgd
31      on ifm.flo_meas_id= ifgd.flo_meas_id
32    join CLARITY.zc_val_type zcvt on ifgd.val_type_c = zcvt.val_type_c
33    where zcvt.name not in (
34      'Blood Pressure', 'Category Type', 'Concentration', 'Custom List',
35      'Date', 'Dose', 'Height', 'Numeric Type', 'Patient Height',
36      'Patient Weight', 'Rate', 'String Type', 'Temperature', 'Time', 'Weight')
37    ), test_key
38  )
39select test_value, test_domain, test_name, sq_result_id.nextval, sysdate
40from test_values
41;
42
43-- TODO:: either handle 'Rate' or check that there is no data with that type.
44
45
46/**
47 * flowsheet_measurement view joins tables as depicted
48 * in "Big Picture" diagram.
49 * to do: indexing on the temp table.
50 */
51
52
53/*
54A note with regard to ip_data_store.pat_id:
55The unique ID of the patient associated with this INP record. This column has
56been deprecated because it may not be updated when patient merges happen. To
57look up the deprecated columns' value after the Clarity Compass upgrade, join
58column IP_DATA_STORE.EPT_CSN to PAT_ENC_HSP. PAT_ENC_CSN_ID and get the PAT_ID
59value.
60*/
61create or replace view flowsheet_measurement as
62select ifr.record_date
63     , ifm.recorded_time
64     , cpeh.pat_id
65     , ifgd.flo_meas_name
66     , ifm.meas_value
67     , ifgd.units
68     -- , ifgd.value_type_name -- deprecated in Clarity 2014 favor using val_type_c
69     , zcvt.name as value_type_name
70     -- , ifgd.multi_select_yn -- deprecated in Clarity 2014 for multi_sel_yn
71     , ifgd.multi_sel_yn
72     , ifm.flo_meas_id
73     , cpeh.pat_enc_csn_id
74     , ifm.entry_time
75     , ifm.fsd_id
76     , ifm.line flowsheet_line
77     -- i2b2 equivalents common to many/all datatypes
78     ,  case
79           when cpeh.pat_enc_csn_id is not null then to_char(cpeh.pat_enc_csn_id)
80           else 'fabricated_for_' || cpeh.pat_id
81         end  ENCOUNTER_IDE
82     , cpeh.pat_id PATIENT_IDE
83     , 'KUH|FLO_MEAS_ID:' || ifm.flo_meas_id CONCEPT_CD
84     , '@' PROVIDER_ID -- todo
85     , recorded_time START_DATE
86     , '@'  MODIFIER_CD
87     -- TODO: test that max(ifm.line) < 100000; it's 7079 as of this writing
88     , ifm.fsd_id * 100000 + ifm.line instance_num
89     , '@' valueflag_cd -- TODO: [H]igh/[L]ow/[A]bnormal
90     , ifgd.units UNITS_CD
91     , ifm.recorded_time END_DATE
92     , '@' LOCATION_CD
93     , to_number(null) CONFIDENCE_NUM -- TODO: think confidence_num through
94     , entry_time UPDATE_DATE
95     ,  mod(ora_hash(cpeh.pat_id), &&heron_etl_chunks)+1 as part
96from CLARITY.ip_flwsht_meas ifm
97  join CLARITY.ip_flwsht_rec ifr
98   on ifr.fsd_id = ifm.fsd_id
99  join CLARITY.ip_flo_gp_data ifgd
100   on ifm.flo_meas_id= ifgd.flo_meas_id
101  join (select min(pat_enc_csn_id) as pat_enc_csn_id,
102      inpatient_data_id, pat_id
103      from clarity.pat_enc cpeh
104      group by inpatient_data_id, pat_id) cpeh
105   on ifr.inpatient_data_id=cpeh.inpatient_data_id
106  left join CLARITY.zc_val_type zcvt on ifgd.val_type_c = zcvt.val_type_c;
107
108
109create or replace view flowsheet_measurement_chunk10k as
110select * from flowsheet_measurement where rownum < 100000;
111
112/* to eyeball flowsheet_measurement:
113select * from flowsheet_measurement_chunk10k
114order by record_date, fsd_id, recorded_time;
115*/
116
117
118
119/**************
120 * numerictypeflows - for value_type_name "Numeric Type" 
121
122
123-- If we're handling numeric flow measures correctly, we
124-- should see an average pulse around 80:
125Ugh... new BLD data doesn't have any PULSE data. Sigh.
126
127select case when abs(avg_pulse - 80) < 15 then 1 else 1/0 end as test_result from (
128select avg(to_number(meas_value)) as avg_bsa
129from flowsheet_measurement_chunk10k
130where flo_meas_id = (
131  select ifgd.flo_meas_id
132  from CLARITY.ip_flo_gp_data ifgd
133  where ifgd.flo_meas_name = 'PULSE'
134  and disp_name is not null)
135);
136 */
137
138
139-- select * from flowsheet_measurement where value_type_name = 'Numeric Type';
140
141
142/* Values such as 2.22222222222222E+22 don't fit in nval_num,
143   which is declared NUMBER(18,5).
144
145   Let's throw out values using E notation, along with nulls,
146   after checking that there are only a handful.
147   In fact, the check doesn't seem worthwhile. Apr 15 results:
148   n=480,544    e=5     tot=74,793,820
149
150   Hmm... that's 0.5% nulls. Worth looking into?
151
152select case
153   when n/tot > 1/100 then 1/0
154   when e/tot > 1/10000 then 1/0
155   else 1
156   end as test_result from (
157select count(null_row) n, count(e_row) e, count(any_row) tot from (
158select
159    case when meas_value is null then 1 else null end as null_row
160  , case when instr(meas_value, 'E+') > 0 then 1 else null end as e_row
161  , 1 as any_row
162from flowsheet_measurement
163where value_type_name = 'Numeric Type'
164);
165*/
166
167create or replace view numerictypeflows as
168select ENCOUNTER_IDE
169      , PATIENT_IDE
170      , CONCEPT_CD
171      , '@' PROVIDER_ID
172      , START_DATE
173      , MODIFIER_CD
174      , instance_num
175      , 'N' VALTYPE_CD
176      , 'E' TVAL_CHAR -- i2b2 doc says 'EQ' but demo data says 'E'
177      , to_number(meas_value) NVAL_NUM
178      , valueflag_cd
179      , UNITS_CD
180      , END_DATE
181      , LOCATION_CD
182      , CONFIDENCE_NUM
183      , UPDATE_DATE
184      , part
185from flowsheet_measurement
186where value_type_name = 'Numeric Type'
187  and meas_value is not null
188  and instr(meas_value, 'E+') = 0
189  /* During one ETL, we found a single row with a non-numeric value which caused
190  the ETL to fail as the to_number call above errored out.  So, filter out
191  non-numeric values (see ticket 1623)
192  */
193  and trim(translate(meas_value, '0123456789-.', ' ')) is null
194;
195
196-- select * from numerictypeflows;
197                     
198
199/***************************
200 * weightmeasureflows - for value_type_name "Weight"
201
202Epic documentation doesn't give units for 'Weight',
203so we've determined emperically that it's oz, so we'll test it:
204Babies weigh about 8 lbs at birth
205
206Ugh... new BLD data has no data on birth weight.
207
208select case when abs(avg_birth_weight - 8) < 2 then 1 else 1/0 end as test_result from (
209select avg(to_number(meas_value)) / 16 as avg_birth_weight
210from flowsheet_measurement_chunk10k
211where flo_meas_id = (
212  select ifgd.flo_meas_id
213  from CLARITY.ip_flo_gp_data ifgd
214  where ifgd.flo_meas_name = 'KU IP ROW OB NB BIRTH WEIGHT'
215  and disp_name is not null)
216);
217 */
218
219-- an oz is 0.0283495231 kg; i.e. 1/16 lb, each of which is about 1/2.2 kg.
220-- We're rounding so as not to imply more precision than was measured.
221
222create or replace view weightmeasureflows as
223Select
224      ENCOUNTER_IDE
225    , PATIENT_IDE
226    , CONCEPT_CD
227    , PROVIDER_ID
228    , START_DATE
229    , MODIFIER_CD
230    , instance_num
231    , 'N' VALTYPE_CD
232    , 'E' TVAL_CHAR
233    , round(meas_value * 0.0283495231, 3) NVAL_NUM
234    , '@' valueflag_cd
235    , 'kg' UNITS_CD
236    , recorded_time END_DATE
237    , LOCATION_CD
238    , CONFIDENCE_NUM
239    , UPDATE_DATE
240    , part
241from flowsheet_measurement
242where value_type_name = 'Weight'
243  and meas_value is not null;
244
245create or replace view patweightmeasureflows as
246Select
247      ENCOUNTER_IDE
248    , PATIENT_IDE
249    , CONCEPT_CD
250    , PROVIDER_ID
251    , START_DATE
252    , MODIFIER_CD
253    , instance_num
254    , 'N' VALTYPE_CD
255    , 'E' TVAL_CHAR
256    , round(meas_value * 0.0283495231, 3) NVAL_NUM
257    , '@' valueflag_cd
258    , 'kg' UNITS_CD
259    , recorded_time END_DATE
260    , LOCATION_CD
261    , CONFIDENCE_NUM
262    , UPDATE_DATE
263    , part
264from flowsheet_measurement
265where value_type_name = 'Patient Weight';
266
267
268/*****************
269 * bpsysmeasureflows - for value_type_name "Blood Pressure" (SYSTOLIC)
270
271Blood pressure syntax: count the mismatches:
272todo: check 'KU IP ROW ARTERIAL BLOOD PRESSURE COMPLEX' too?
273 */
274select case count(*) when 0 then 1 else 1/0 end as test_result from (
275select *
276from flowsheet_measurement_chunk10k
277where flo_meas_id in (
278  select ifgd.flo_meas_id
279  from CLARITY.ip_flo_gp_data ifgd
280  where ifgd.flo_meas_name = 'BLOOD PRESSURE'
281  and disp_name is not null)
282  and not regexp_like(meas_value, '[0-9]+/[0-9]+')
283);
284                     
285create or replace view bpsysmeasureflows as
286Select
287      ENCOUNTER_IDE
288    , PATIENT_IDE
289    , 'KUH|FLO_MEAS_ID:' || flo_meas_id || '_' || 'SYSTOLIC' CONCEPT_CD
290    , PROVIDER_ID
291    , START_DATE
292    , MODIFIER_CD
293    , instance_num
294    , 'N' VALTYPE_CD
295    , 'E' TVAL_CHAR
296    , substr(meas_value, 1, instr(meas_value, '/') -1 ) NVAL_NUM
297    , '@' valueflag_cd
298    , 'mmHg' UNITS_CD
299    , recorded_time END_DATE
300    , LOCATION_CD
301    , CONFIDENCE_NUM
302    , UPDATE_DATE
303    , part
304from  flowsheet_measurement
305where value_type_name = 'Blood Pressure'
306  and meas_value is not null;
307 
308/**
309 * bpdiameasureflows --  for value_type_name "Blood Pressure" (DIASTOLIC)
310 */
311
312create or replace view bpdiameasureflows as
313Select
314      ENCOUNTER_IDE
315    , PATIENT_IDE
316    , 'KUH|FLO_MEAS_ID:' || flo_meas_id || '_' || 'DIASTOLIC' CONCEPT_CD
317    , PROVIDER_ID
318    , START_DATE
319    , MODIFIER_CD
320    , instance_num
321    , 'N' VALTYPE_CD
322    , 'E' TVAL_CHAR
323    , substr(meas_value, instr(meas_value, '/') + 1 ) NVAL_NUM
324    , '@' valueflag_cd
325    , 'mmHg' UNITS_CD
326    , recorded_time END_DATE
327    , LOCATION_CD
328    , CONFIDENCE_NUM
329    , UPDATE_DATE
330    , part
331from  flowsheet_measurement
332where value_type_name = 'Blood Pressure'
333  and meas_value is not null;
334
335
336/*****************
337 * heightmeasureflows - for value_type_name "Height"
338
339  The average total body length is 14-20 inches (35.6-50.8 cm),
340  although premature newborns may be much smaller.
341  -- http://en.wikipedia.org/wiki/Infant
342
343They're recorded in inches, not cm, right?
344 */
345select case when abs(avg_height - 72) < 15 then 1 else 1/0 end as test_result from (
346select avg(to_number(meas_value)) as avg_height
347from flowsheet_measurement_chunk10k
348where flo_meas_id = (
349  select ifgd.flo_meas_id
350  from CLARITY.ip_flo_gp_data ifgd
351  where ifgd.flo_meas_name = 'HEIGHT'
352  and disp_name is not null)
353);
354
355create or replace view heightmeasureflows as
356Select
357      ENCOUNTER_IDE
358    , PATIENT_IDE
359    , CONCEPT_CD
360    , PROVIDER_ID
361    , START_DATE
362    , MODIFIER_CD
363    , instance_num
364    , 'N' VALTYPE_CD
365    , 'E' TVAL_CHAR
366    , meas_value*2.54   NVAL_NUM
367    , '@' valueflag_cd
368    , 'cm' UNITS_CD
369    , recorded_time END_DATE
370    , LOCATION_CD
371    , CONFIDENCE_NUM
372    , UPDATE_DATE
373    , part
374from  flowsheet_measurement
375where value_type_name = 'Height'
376  and meas_value is not null;
377
378/*******************
379 * patheightmeasureflows - for value_type_name "Patient Height"
380 */
381create or replace view patheightmeasureflows as
382Select
383      ENCOUNTER_IDE
384    , PATIENT_IDE
385    , CONCEPT_CD
386    , PROVIDER_ID
387    , START_DATE
388    , MODIFIER_CD
389    , instance_num
390    , 'N' VALTYPE_CD
391    , 'E' TVAL_CHAR
392    , meas_value*2.54   NVAL_NUM
393    , '@' valueflag_cd
394    , 'cm' UNITS_CD
395    , recorded_time END_DATE
396    , LOCATION_CD
397    , CONFIDENCE_NUM
398    , UPDATE_DATE
399    , part
400from  flowsheet_measurement
401where value_type_name = 'Patient Height'
402  and meas_value is not null;
403
404 
405/***********
406 * datemeasureflows - for value_type_name "Date"
407 *
408 * STRANGE! Syntax is EITHER:
409 *  - a number of days since Dec 31, 1840 or
410 *  - a date in 'MM/DD/YYYY' format
411 */
412create or replace view datemeasureflows as
413Select
414      ENCOUNTER_IDE
415    , PATIENT_IDE
416    , CONCEPT_CD
417    , PROVIDER_ID
418    , START_DATE
419    , MODIFIER_CD
420    , instance_num
421    , 'D' VALTYPE_CD, 
422      case
423          when substr(meas_value , 1, instr(meas_value , '/') -1 ) is NULL
424          then to_char(to_date('12/31/1840', 'MM/DD/YYYY') + meas_value, 'YYYY-MM-DD')
425          else to_char(to_date(meas_value, 'MM/DD/YYYY'), 'YYYY-MM-DD')
426      end TVAL_CHAR
427    , null NVAL_NUM
428    , '@' valueflag_cd
429    , UNITS_CD
430    , recorded_time END_DATE
431    , LOCATION_CD
432    , CONFIDENCE_NUM
433    , UPDATE_DATE
434    , part
435from  flowsheet_measurement
436where value_type_name = 'Date'
437  and meas_value is not null;
438
439
440/* todo:: test date-shifting when VALUE_TYPE_NAME='Date'
441        e.g. 11568 or 700 PLACEMENT DATE (#299) */
442         
443/** timemeasureflows -  for value_type_name "Time"
444
445We infer from eyeballing the data that "Time" measurements are in seconds,
446but since we can't confirm from Epic documentation, we test that
447the maximum is less than 24 hours:
448
449 */
450select case when max_hrs < 24 then 1 else 1/0 end as test_result from (
451select max(to_number(meas_value)) / 60 / 60 as max_hrs
452from flowsheet_measurement_chunk10k
453where value_type_name = 'Time'
454);
455
456
457create or replace view timemeasureflows as
458Select
459      ENCOUNTER_IDE
460    , PATIENT_IDE
461    , CONCEPT_CD
462    , PROVIDER_ID
463    , START_DATE
464    , MODIFIER_CD
465    , instance_num
466    , 'N' VALTYPE_CD
467    , 'E' TVAL_CHAR
468    , meas_value NVAL_NUM
469    , '@' valueflag_cd
470    , 's' UNITS_CD
471    , recorded_time END_DATE
472    , LOCATION_CD
473    , CONFIDENCE_NUM
474    , UPDATE_DATE 
475    , part
476from  flowsheet_measurement
477where value_type_name = 'Time'
478  and meas_value is not null;
479
480
481/*************
482 * cattypeflows - for value_type_name "Category Type"
483
484select * from flowsheet_measurement
485where value_type_name = 'Category Type'
486  and meas_value is not null;
487
488TODO: what exactly is a CategoryType? It looks numeric, but surely
489those numbers are symbolic references to rows elsewhere in the database.
490
491some examples:
492
493where flo_meas_id= 7
494and flo_meas_name <> 'TEMP SOURCE';
495
496where flo_meas_id= 9599
497and flo_meas_name <> 'KU IP ROW OB DELIVERY METHOD';
498
499exploration...
500
501-- NOTE: Category Item is deprecated in Clarity 2014 in favor of CAT_ITEM
502select ifgd.category_item, ifgd.* from CLARITY.ip_flo_gp_data ifgd
503where ifgd.category_item is not null;
504
505select * from clarity_col8 where column_description like '%category%'
506and column_name like 'CATEG%';
507
508select * from clarity_col8 where column_description like '%CategoryType%';
509
510select * from clarity.concept_category;
511*/
512
513create or replace view cattypeflows as
514Select
515      ENCOUNTER_IDE
516    , PATIENT_IDE
517    , CONCEPT_CD
518    , PROVIDER_ID
519    , START_DATE
520    , MODIFIER_CD
521    , instance_num
522    , 'N' VALTYPE_CD
523    , 'E' TVAL_CHAR
524    , meas_value NVAL_NUM
525    , '@' valueflag_cd
526    , recorded_time END_DATE
527    , units UNITS_CD
528    , LOCATION_CD
529    , CONFIDENCE_NUM
530    , UPDATE_DATE
531    , part
532from  flowsheet_measurement
533where value_type_name = 'Category Type'
534  and meas_value is not null;
535
536
537/*************
538 * Temperatureflows - for value_type_name "Temperature"
539
540We should convert temperature from F to C, right?
541
542Ugh... no BLD data.
543
544select case when abs(avg_temp - 98.6) < 4 then 1 else 1/0 end as test_result from (
545select avg(to_number(meas_value)) as avg_temp
546from flowsheet_measurement_chunk10k
547where flo_meas_id = (
548  select ifgd.flo_meas_id
549  from CLARITY.ip_flo_gp_data ifgd
550  where ifgd.flo_meas_name = 'TEMPERATURE'
551  and disp_name is not null)
552);
553 */
554
555create or replace view Temperatureflows as
556Select
557      ENCOUNTER_IDE
558    , PATIENT_IDE
559    , CONCEPT_CD
560    , PROVIDER_ID
561    , START_DATE
562    , MODIFIER_CD
563    , instance_num
564    , 'N' VALTYPE_CD
565    , 'E' TVAL_CHAR
566    , round((5/9 *(meas_value - 32)), 1) NVAL_NUM
567    , '@' valueflag_cd
568    , 'C' UNITS_CD
569    , recorded_time END_DATE
570    , LOCATION_CD
571    , CONFIDENCE_NUM
572    , entry_time UPDATE_DATE
573    , part
574from  flowsheet_measurement
575where value_type_name = 'Temperature'
576  and meas_value is not null;
577
578
579/****************
580 * stringtypeflows -- for value_type_name "String Type"
581 */
582create or replace view stringtypeflows as
583Select
584      ENCOUNTER_IDE
585    , PATIENT_IDE
586    , CONCEPT_CD
587    , PROVIDER_ID
588    , START_DATE
589    , MODIFIER_CD
590    , instance_num
591    , 'Ti' VALTYPE_CD -- only add ID information to nightheron
592    , meas_value TVAL_CHAR
593    , to_number(null) NVAL_NUM
594    , '@' valueflag_cd
595    , '@' UNITS_CD
596    , recorded_time END_DATE
597    , LOCATION_CD
598    , CONFIDENCE_NUM
599    , UPDATE_DATE
600    , part
601from  flowsheet_measurement
602where value_type_name = 'String Type'
603  and meas_value is not null;
604
605
606/*******************
607 * concentrationflows - for value_type_name "Concentration"
608 *
609 * NOTE: No data in ClarityB to test the validity
610 */
611create or replace view concentrationflows as
612Select
613      ENCOUNTER_IDE
614    , PATIENT_IDE
615    , CONCEPT_CD
616    , PROVIDER_ID
617    , START_DATE
618    , MODIFIER_CD
619    , instance_num
620    , 'T' VALTYPE_CD -- ????
621    , meas_value TVAL_CHAR
622    , to_number(null) NVAL_NUM
623    , UNITS_CD
624    , LOCATION_CD
625    , CONFIDENCE_NUM
626    , UPDATE_DATE
627    , part
628from  flowsheet_measurement
629where value_type_name = 'Concentration'
630  and meas_value is not null;
631
632
633-------------------------------------------------------------------------------------------------------------------------------
634
635-- This query is load data for value_type_name "Dose"
636------------------------------------------------------------
637
638-- Test cases  (should return 0 rows)
639--- No data in ClarityB to test the validity
640
641
642create or replace view doseflows as
643Select
644      ENCOUNTER_IDE
645    , PATIENT_IDE
646    , CONCEPT_CD
647    , PROVIDER_ID
648    , START_DATE
649    , MODIFIER_CD
650    , instance_num
651    , 'T' VALTYPE_CD
652    , meas_value TVAL_CHAR
653    , to_number(null) NVAL_NUM
654    , '0' UNITS_CD
655    , LOCATION_CD
656    , CONFIDENCE_NUM
657    , UPDATE_DATE
658    , part
659from  flowsheet_measurement
660where value_type_name = 'Dose'
661  and meas_value is not null;
662 
663 
664
665/***************************************************
666 * singleselectflows -- for value_type_name 'Custom List' with multi select 'N'
667 */
668-- 1st, a utility view
669create or replace view custom_list_match as
670select
671  ifcl.flo_meas_id, ifcl.line,
672  (case when ifcl.abbreviation is null then ifcl.custom_value
673   else ifcl.abbreviation end) val
674from CLARITY.ip_flo_cust_list ifcl;
675
676-- select * from custom_list_match
677
678create or replace view singleselectflows as
679Select
680      ENCOUNTER_IDE
681    , PATIENT_IDE
682    , (case when clm.line is not null
683        then 'KUH|FLO_MEAS_ID+LINE:' || fm.flo_meas_id || '_' || clm.line
684        else 'KUH|FLO_MEAS_ID:' || fm.flo_meas_id
685       end) CONCEPT_CD
686    , PROVIDER_ID
687    , START_DATE
688    , MODIFIER_CD
689    , instance_num
690    , '@' VALTYPE_CD
691    , '@' TVAL_CHAR
692    , to_number(null) NVAL_NUM
693    , valueflag_cd
694    , UNITS_CD
695    , END_DATE
696    , LOCATION_CD
697    , CONFIDENCE_NUM
698    , UPDATE_DATE
699    , part
700from flowsheet_measurement fm
701  left outer join custom_list_match clm
702   on fm.flo_meas_id = clm.flo_meas_id
703     and  fm.meas_value = clm.val
704where (multi_sel_yn is null or multi_sel_yn = 0)
705  and value_type_name = 'Custom List';
706
707/*
708
709select * from singleselectflows
710where rownum < 200;
711
712explain plan for
713
714select count(*) from singleselectflows;
715
716SELECT PLAN_TABLE_OUTPUT FROM TABLE(DBMS_XPLAN.DISPLAY());
717
718119476200
719*/
720 
721
722/***************************
723 * multiselectflows -- for value_type_name 'Custom List' with multi select 'Y'
724
725See also: epic_flowsheets_multiselect.sql
726 */
727
728whenever sqlerror continue;
729drop table flow_measure_multi;
730whenever sqlerror exit;
731create table flow_measure_multi as
732select fm.meas_value
733     , fm.flo_meas_id
734     , ifcl.custom_value choice
735     , case
736         when ifcl.line is null then ora_hash('abc')
737        else null
738       end choice_hash
739     , ifcl.line
740from flowsheet_measurement fm
741left join CLARITY.ip_flo_cust_list ifcl
742       on ifcl.flo_meas_id = fm.flo_meas_id
743where 1=0;
744
745
746create or replace view multiselectflows as
747Select ENCOUNTER_IDE
748     , PATIENT_IDE
749     , (case when fmm.line is not null
750        then 'KUH|FLO_MEAS_ID+LINE:' || fm.flo_meas_id || '_' || fmm.line
751        else 'KUH|FLO_MEAS_ID+hash:' || fm.flo_meas_id || '_' || fmm.choice_hash
752        end)  CONCEPT_CD
753     , PROVIDER_ID
754     , START_DATE
755     , MODIFIER_CD
756    , instance_num
757     , '@' VALTYPE_CD
758     , '@' TVAL_CHAR
759     , to_number(null) NVAL_NUM
760     , valueflag_cd
761     , UNITS_CD
762     , END_DATE
763     , LOCATION_CD
764     , CONFIDENCE_NUM
765     , UPDATE_DATE
766     , part
767from flowsheet_measurement fm
768  join flow_measure_multi fmm
769    on fm.meas_value = fmm.meas_value
770    and fm.flo_meas_id = fmm.flo_meas_id
771where fm.multi_sel_yn = 1
772  and fm.value_type_name = 'Custom List';
773
774-- select * from multiselectflows where rownum < 200;
Note: See TracBrowser for help on using the repository browser.