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/etl_tests_report.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.0 KB
Line 
1/* etl_tests_report -- Generated report for ETL tests
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
7References:
8  * KUMC Informatics ticket #2781
9*/
10
11/* Consider storing results somewhere persistent - maybe use a sequence value as
12a key and include a timestamp and maybe a release name or some such user defined
13string.
14
15Note: Previous designs created etl_tests_report as a table.  So, leave the drop
16statement here for cleaning up after old builds.
17*/
18whenever sqlerror continue;
19drop table etl_tests_report;
20whenever sqlerror exit;
21
22create or replace view etl_tests_report as
23select
24  test_result, test_domain || '_' || realm test_domain, test_name, test_value,
25  result_id, result_date, lower_bound, upper_bound, units, test_description,
26  max_result_id
27from (
28  select
29    case
30      when ((et.lower_bound is not null and tv.test_value < et.lower_bound)
31        or (et.upper_bound is not null and tv.test_value > et.upper_bound))
32      then 'FAIL' else decode(coalesce(to_char(et.upper_bound),
33                                       to_char(et.lower_bound),
34                                       et.test_domain),
35                              null, null,
36                              et.test_domain, 'fyi',
37                              'ok')
38    end test_result,
39    tv.*, et.lower_bound, et.upper_bound, et.units, et.test_description,
40    max(result_id) over(partition by tv.test_domain, tv.test_name, tv.realm) max_result_id
41    from (
42      select 'id' realm, vals.* from etl_test_values vals
43      union all
44      select 'deid' realm, vals.* from etl_test_values@deid vals
45      ) tv
46    -- We still want to know test results that don't have an entry in the .csv
47    left join etl_tests et on et.test_domain = tv.test_domain and et.test_name = tv.test_name
48  )
49where result_id = max_result_id
50order by test_result
51;
Note: See TracBrowser for help on using the repository browser.