source: heron_load/epic_med_mapping.sql

Last change on this file was 0:cf3d0387c257, checked in by Nathan Graham <ngraham@…>, 4 months ago

Oops. schema_type_to_name() returns a tuple now.

File size: 23.3 KB
Line 
1/* epic_med_mapping -- Map Epic meds to standard terminology RxNorm/NDF-RT
2
3Copyright (c) 2014 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
7Hierarchy based on "Drug Products by VA Class".  This was the organization
8suggested by "2010 paper on mashing up RxNorm and NDF-RT hierarchy" found here:
9  http://www.ncbi.nlm.nih.gov/pubmed/21347044
10
11The RxNorm documentation is located here:
12http://www.nlm.nih.gov/research/umls/rxnorm/docs/2012/rxnorm_doco_full_2012-3.html
13
14Desired hierarchy:
15
16VA Drug Class (NDF-RT)
17  |- Semantic Clinical Drug and Form (SCDF)/Semantic Branded Drug and Form (SBDF) from RxNorm
18    |- Clarity Drug
19
20Notes about manual curation:
21med_map_manual_curation is a table that maps a clarity medication ID to a parent
22concept.  It is designed to be used for medications that we couldn't map to RxNORM
23directly from information in Clarity (via GCN, NDC, etc). This parent could be a
24VA class or an SCDF/SBDF.
25
26The table has the following columns:
27CLARITY_MEDICATION_ID - the id of the med without direct linkage to RxNORM
28CLARITY_NAME (not used for mapping - just for eyeballing)
29FACTS (not used for mapping - just for eyeballing)
30PATIENTS (not used for mapping - just for eyeballing)
31VA_NAME (not used for mapping - just for eyeballing)
32VA_RXAUI - If we didn't map to an SDF AUI (below), the fall back on this one for VA class.
33SDF_RXAUI - Map here if not null, otherwise fall back on the VA_RXAUI (above)
34SDF_NAME (not used for mapping - just for eyeballing)
35
36Note that this code uses RxNORM relationships even if the concept is "suppressed". 
37
38From:
39http://www.nlm.nih.gov/research/umls/rxnorm/docs/2014/rxnorm_doco_full_2014-3.html#conso
40   
41    "Suppressible flag. Values = N, O, Y, or E.
42    N - not suppressible.
43    O - Specific individual names (atoms) set as Obsolete because the name is no
44         longer provided by the original source.
45    Y - Suppressed by RxNorm editor.
46    E - unquantified, non-prescribable drug with related quantified, prescribable
47        drugs. NLM strongly recommends that users not alter editor-assigned
48        suppressibility."
49
50TODOs:
51 - Consider MedEx NLP for meds that didn't match to an RxCUI.
52 - Consider medications that might map to a VA class directly but not an SCDF/SBDF.
53*/
54
55
56-- Check access: should be running on nheron
57select upload_id from NIGHTHERONDATA.observation_fact where 1=0;
58
59-- Check access to KUMC/rxnorm
60select rxcui from rxnorm.rxnconso where 1=0;
61
62-- Audit constants for stuff from RxNorm
63create or replace view rxnorm_audit_info as
64select * from BlueHeronData.source_master@deid
65where source_cd like 'rxnorm@%';
66
67/* Map our clarity medication id to GCN and then to RxCUI
68*/
69create or replace view clarity_med_id_to_rxcui_gcn as select * from(
70  with med_id_to_gcn as(
71    /* Map the clarity medication ID to the clarity GCN.  Note that there may be
72    multiple GCNs for a single med id which means it may end up in more than one
73    place in the hierarchy.
74    */
75    select
76      ccm.medication_id, crmg.gcn_seqno, ccm.name
77    from clarity.clarity_medication ccm
78    join clarity.rx_med_gcnseqno crmg on crmg.medication_id = ccm.medication_id
79    ),
80  gcn_to_rxui as(
81    --Map the clarity GCN to the RxNorm GCN/CUI
82    select
83      mtog.medication_id, mtog.gcn_seqno, mtog.name clarity_name, rxc.code,
84      rxc.rxcui, rxc.tty, rxc.str rxname
85    from med_id_to_gcn mtog
86    join rxnorm.rxnconso rxc on rxc.code = lpad(mtog.gcn_seqno, 6, '0')
87    where rxc.sab='NDDF' and rxc.tty != 'IN' --skip ingredients (#1246 comment:13)
88    )
89  select
90    medication_id clarity_med_id, gcn_seqno clarity_gcn, code rxn_gcn, rxcui, tty,
91    clarity_name, rxname
92  from gcn_to_rxui
93  );
94
95
96/* Map our clarity medication id to NDC and then to an RxCUI
97*/
98create or replace view clarity_med_id_to_rxcui_ndc as select * from(
99  with med_id_to_ndc as(
100    /* Map the clarity medication ID to the clarity NDC.  Note that there may be
101    multiple NDCs for a single med id which means it may end up in more than one
102    place in the hierarchy.
103   
104    Note that we don't currently handle NDC normalization.  See:
105    http://www.nlm.nih.gov/research/umls/rxnorm/NDC_Normalization_Code.rtf
106    */
107    select
108      ccm.medication_id, cndc.ndc_code, ccm.name clarity_name
109    from clarity.clarity_medication ccm
110    join clarity.clarity_ndc_codes cndc on cndc.medication_id=ccm.medication_id
111    ),
112  ndc_to_rxui as(
113    --Map the clarity NDC to the RxNorm NDC/CUI
114    select
115      mton.medication_id, mton.ndc_code, sat.rxcui, con.str rxname, clarity_name
116    from med_id_to_ndc mton
117    join rxnorm.rxnsat sat on sat.atv = mton.ndc_code
118    join rxnorm.rxnconso con on sat.rxcui = con.rxcui
119    where sat.atn='NDC' --and con.tty != 'IN' --skip ingredients (#1246 comment:13)
120    )
121  select
122    medication_id clarity_med_id, ndc_code, clarity_name, rxcui, rxname
123  from ndc_to_rxui
124  ); 
125
126
127/* Map Clarity medications to RxNorm (Clarity medication_id to rxcui).
128Temporary tables are used instead of views to reduce run time.
129*/
130whenever sqlerror continue;
131drop table clarity_med_id_to_rxcui;
132whenever sqlerror exit;
133
134create table clarity_med_id_to_rxcui as(
135  select distinct
136    clarity_med_id, rxcui, clarity_name, 'GCN' as src
137  from
138    clarity_med_id_to_rxcui_gcn
139
140  union
141
142  select distinct
143    clarity_med_id, rxcui, clarity_name, 'NDC' as src
144  from
145    clarity_med_id_to_rxcui_ndc
146  );
147
148commit;
149
150
151/* Constants used later in the query for creating concept codes
152*/
153create or replace view med_constants as
154  select
155    'RXCUI:' rxcui_prefix,
156    'RXAUI:' rxaui_prefix,
157    'MEDICATION_ID:' local_med_id_prefix,
158    'KUH|MEDICATION_ID:' med_concept_prefix,
159    'LA' leaf,
160    'FA' folder,
161    3149154 va_parent_aui, -- "Drug Products by VA Class" (RxAUI = 3149154)
162    'Medications' meds_top_level,
163    'Other Medication Concepts' other_meds_bucket
164  from dual
165;
166
167/* Parent/child relationships for the VA class hierarchy
168*/
169create or replace view va_classes as (
170  select * from (
171    select
172      con1.str va_parent_name, con1.rxcui va_parent_rxcui, con1.rxaui va_parent_rxaui,
173      rel.rel,
174      con2.str va_child_name, con2.rxcui va_child_rxcui, con2.rxaui va_child_rxaui, c.folder visualattributes
175    from rxnorm.rxnrel rel
176    join rxnorm.rxnconso con1 on con1.rxaui=rel.rxaui1
177    join rxnorm.rxnconso con2 on con2.rxaui=rel.rxaui2
178    join rxnorm.rxnsat sat2 on sat2.code = con2.scui, med_constants c
179    where rel.sab = 'NDFRT' and rel.rel = 'CHD' and sat2.atv = 'VA Class'
180     
181    union all
182     
183    select
184      null va_parent_name, null va_parent_rxcui, null va_parent_rxaui,
185      'CHD' rel,
186      con.str va_child_name, con.rxcui va_child_rxcui, con.rxaui va_child_rxaui, c.folder visualattributes
187    from rxnorm.rxnconso con, med_constants c
188    where con.rxaui = c.va_parent_aui
189    )
190  where va_child_rxaui not in (
191    /* Skip "[AA000] INTRODUCTION" as it doesn't seem relevant for our medication
192    hierarchy.
193    */
194    3257489
195    )
196  )
197;
198
199
200/* Find all SCDF/SBDF concepts from RxNorm
201*/
202whenever sqlerror continue;
203truncate table sdf_children;
204drop table sdf_children;
205whenever sqlerror exit;
206
207create table sdf_children as (
208  select * from ( -- extra select because Oracle says "SQL Error: ORA-32034: unsupported use of WITH clause" otherwise
209    with scdf_sbdf as (
210      select
211        con1.str sdf_name, con1.rxcui sdf_rxcui, con1.rxaui sdf_rxaui, con1.sab sdf_sab,
212        con2.str isa_name, con2.rxcui isa_rxcui, con2.rxaui isa_rxaui, con2.sab isa_sab
213      /* None of the concepts marked as SCDF are direct children of any of the VA classes.  So, utilze the 'isa'
214      relationship.
215      */
216      from rxnorm.rxnrel rel
217      join rxnorm.rxnconso con1 on con1.rxcui=rel.rxcui1
218      join rxnorm.rxnconso con2 on con2.rxcui=rel.rxcui2
219      where rel.rela = 'isa' and (con1.tty = 'SCDF' or con1.tty = 'SBDF')
220      )
221    /* Parent/child relationships for SCDF/SBDF to VA class
222    */
223    select distinct --Lots of things match the 'isa' criteria - just need the distinct SCDF/SBDF
224      va.va_child_name va_name, va.va_child_rxcui va_rxcui, va.va_child_rxaui va_rxaui,
225      rel.rel,
226      sdf.sdf_name, sdf.sdf_rxcui, sdf.sdf_rxaui, c.folder visualattributes
227    from rxnorm.rxnrel rel
228    join va_classes va on va.va_child_rxaui=rel.rxaui1
229    join scdf_sbdf sdf on sdf.isa_rxaui = rel.rxaui2 and rel.rel = 'CHD', med_constants c
230    -- where sdf.sdf_sab = 'RXNORM' --Note, all SCDF/SBDF that join on rxaui are from RXNORM
231  )
232); 
233 
234 
235/* Distinct mapping of clarity mediation id to SCDF/SBDF
236*/
237whenever sqlerror continue;
238truncate table clarity_children;
239drop table clarity_children;
240whenever sqlerror exit;
241
242create table clarity_children as (
243  select * from (
244    with
245    clarity_matches as (
246      select distinct -- Many RxCUI matches for a single medication - we just need one
247        con1.rxaui sdf_aui, cmed.clarity_med_id
248      from rxnorm.rxnrel rel
249      join rxnorm.rxnconso con1 on con1.rxcui=rel.rxcui1
250      join rxnorm.rxnconso con2 on con2.rxcui=rel.rxcui2
251      join sdf_children sdf on sdf.sdf_rxaui = con1.rxaui
252      join clarity_med_id_to_rxcui cmed on cmed.rxcui = rel.rxcui2
253      where rel.rela = 'isa'
254      )
255    /* Parent/child relationships for clarity medication id to SCDF/SBDF
256    */
257    select
258      con.str sdf_name, con.rxcui sdf_rxcui, con.rxaui sdf_rxaui,
259      cmed.name clarity_name, cmed.medication_id clarity_med_id, c.leaf visualattributes
260    from clarity_matches cmatch
261    join rxnorm.rxnconso con on con.rxaui = cmatch.sdf_aui
262    join clarity.clarity_medication cmed on cmed.medication_id = cmatch.clarity_med_id, med_constants c
263  )
264);
265
266/* Create i2b2-style terms
267*/
268whenever sqlerror continue;
269truncate table rxnorm_terms;
270drop table rxnorm_terms;
271whenever sqlerror exit;
272
273
274create table rxnorm_terms as
275select
276  lvl c_hlevel, concept_path c_fullname, child_name as c_name,
277  visualattributes c_visualattributes, child_concept c_basecode,
278  concept_path c_dimcode, sysdate update_date, sysdate download_date,
279  sysdate import_date, aud.source_cd sourcesystem_cd, norm.*
280from (
281  select level + 1 as lvl,
282  '\i2b2\' || mc.meds_top_level || SYS_CONNECT_BY_PATH(child_uid, '\') || '\' as concept_path,
283  parent_child.*
284  from med_constants mc, (
285    /* VA Class parent/child */
286    select
287      va_parent_name parent_name, rxaui_prefix || va_parent_rxaui parent_uid, 
288      rxcui_prefix || va_parent_rxcui parent_concept,
289      va_child_name child_name, rxaui_prefix || va_child_rxaui child_uid,
290      rxcui_prefix || va_child_rxcui child_concept, visualattributes
291    from va_classes, med_constants
292    union all
293    /* SCDF/SBDF to VA Class */
294    select
295      va_name parent_name, rxaui_prefix || va_rxaui parent_uid,
296      rxcui_prefix || va_rxcui parent_concept,
297      sdf_name child_name, rxaui_prefix || sdf_rxaui child_uid,
298      rxcui_prefix || sdf_rxcui child_concept, visualattributes
299    from sdf_children, med_constants
300    union all
301    /* Clarity medications to SCDF/SBDF */
302    select
303      sdf_name parent_name, rxaui_prefix || sdf_rxaui parent_uid,
304      rxcui_prefix || sdf_rxcui parent_concept,
305      clarity_name child_name, local_med_id_prefix || to_char(clarity_med_id) child_uid,
306      med_concept_prefix || clarity_med_id child_concept,
307      visualattributes
308    from clarity_children, med_constants
309    union all
310    /* Manual curation */
311    select
312      con.str parent_name, rxaui_prefix || con.rxaui parent_uid,
313      rxcui_prefix || con.rxcui parent_concept,
314      cm.name child_name, local_med_id_prefix || to_char(clarity_medication_id) child_uid,
315      med_concept_prefix || cm.medication_id child_concept,
316      'LA ' visualattributes
317    from med_constants, (
318      select distinct --duplications in curated data?
319        clarity_medication_id, coalesce(sdf_rxaui, va_rxaui) parent_rxaui
320      from med_map_manual_curation@deid
321      where clarity_medication_id is not null
322      )
323    join clarity.clarity_medication cm on cm.medication_id = clarity_medication_id
324    join rxnorm.rxnconso con on con.rxaui = parent_rxaui
325    union all
326    /* Bucket for "other" medications.
327    TODO: Consider organizing these "other" medications into smaller buckets.
328    For example, have alphabetical folders based on medication name - something
329    like "A-D", "E-H", etc.
330    */
331    select
332      con.str parent_name, rxaui_prefix || con.rxaui parent_uid,
333      mc.rxcui_prefix || con.rxcui parent_concept,
334      mc.other_meds_bucket child_name, mc.other_meds_bucket child_uid,
335      null child_concept,
336      mc.folder
337    from med_constants mc, rxnorm.rxnconso con
338    where con.rxaui = mc.va_parent_aui       
339  ) parent_child
340  start with parent_child.parent_uid = rxaui_prefix || mc.va_parent_aui
341  connect by prior parent_child.child_uid = parent_child.parent_uid
342  ), rxnorm_audit_info aud, BlueHeronMetadata.normal_concept@deid norm
343;
344
345commit;
346
347/* Make sure that medications we mapped to the RxNorm ontology cover nearly all
348of the medication facts we have. 
349
350Note that in previous versions of the code this test came _after_ we included
351the top 500 medications by fact count in the "Other Medications" folder.  So,
352previously things in the "Other Medications" folder were considered mapped to
353RxNorm.
354*/
355select case when mapped_fact_pct < 98 then 1/0 else 1 end most_meds_mapped_pass, rslt.* from (
356  with
357  med_counts as (
358    select substr(concept_cd, length(mc.med_concept_prefix)+1) med_id, facts, patients
359    from blueheronmetadata.counts_by_concept@deid, med_constants mc
360    where concept_cd like mc.med_concept_prefix || '%'
361    ),
362  mapped_meds as (
363    select distinct substr(c_basecode, length(mc.med_concept_prefix)+1) med_id
364    from rxnorm_terms, med_constants mc
365    where c_basecode like mc.med_concept_prefix || '%'
366    ),
367  mapped_counts as (
368    select * from med_counts mc
369    join mapped_meds mm on mm.med_id = mc.med_id
370    ),
371  total_fact_count as (
372    select sum(facts) cnt from med_counts
373    ),
374  mapped_fact_count as (
375    select sum(facts) cnt from mapped_counts
376    )
377  select
378    tf.cnt total_facts,
379    mp.cnt mapped_counts,
380    round((mp.cnt / tf.cnt) * 100) mapped_fact_pct
381  from mapped_fact_count mp, total_fact_count tf
382  ) rslt
383;
384
385
386/* Test to make sure that KUH|MEDICATION_ID:210319 = "CEFAZOLIN INJ 1GM IVP" is
387in the right place in the hierarchy (see ticket 1451).
388"CEFAZOLIN INJ 1GM IVP" in clarity is a "Cefazolin Injectable Solution"
389*/
390select case when count(*) = 0 then 1/0 else 1 end med_match_by_name_1451 from (
391  select c_name clarity_name, str rxnorm_sdf from (
392    /* Get the parent RXAUI from the fullname - like RXAUI:1576284 from
393    \i2b2\Medications\RXAUI:3257505\RXAUI:3257510\RXAUI:3257511\RXAUI:1576284\MEDICATION_ID:210319\
394    */
395    select
396      trim('\' from regexp_substr(c_fullname,'\\[^\\]+\\', instr(c_fullname, '\', -1, 3))) sdf_rxaui,
397      c_name
398    from rxnorm_terms
399    where c_basecode = 'KUH|MEDICATION_ID:210319'
400    ) mp
401  -- Join with RxNorm for eyeballing
402  join rxnorm.rxnconso rxc on (select rxaui_prefix from med_constants) || rxc.rxaui = mp.sdf_rxaui
403  where rxc.rxaui = 1576284 --Cefazolin Injectable Solution
404  );
405
406
407create or replace view mapped_meds as
408select
409  distinct substr(rxt.c_basecode, length(mc.med_concept_prefix)+1) med_id
410from rxnorm_terms rxt, med_constants mc
411where rxt.c_basecode like mc.med_concept_prefix || '%';
412 
413create or replace view unmapped_meds as
414with
415mpl as (
416  --Ugh, I couldn't use the view directly in a length() function directly for some reason
417  select(length((select med_concept_prefix from med_constants))) l from dual
418  ),
419med_counts as (
420  select substr(cbc.concept_cd, mpl.l + 1) clarity_med_id, cbc.facts, cbc.patients
421    from mpl, med_constants mc, blueheronmetadata.counts_by_concept@deid cbc
422    where cbc.concept_cd like mc.med_concept_prefix || '%'
423  )
424select cmed.medication_id clarity_medication_id, cmed.name clarity_name, mc.facts, mc.patients
425from med_counts mc
426left join mapped_meds mm on mm.med_id = mc.clarity_med_id 
427join clarity.clarity_medication cmed on to_char(cmed.medication_id) = mc.clarity_med_id
428where mm.med_id is null;
429
430
431/* Put the top 500 unmapped medications (by fact count) in the "other" bucket
432The query construction might seem odd, but writing it this way increased
433performance dramatically.
434*/
435insert into rxnorm_terms
436with
437other_meds as (
438  select * from
439    rxnorm_terms rt, med_constants mc, unmapped_meds um
440  where rt.c_name = mc.other_meds_bucket
441  )
442select
443  c_hlevel + 1 c_hlevel, c_fullname || clarity_medication_id || '\' c_fullname,
444  clarity_name c_name, leaf c_visualattributes,
445  med_concept_prefix || clarity_medication_id c_basecode,
446  c_fullname || clarity_medication_id || '\' c_dimcode, update_date,
447  download_date, import_date, sourcesystem_cd, norm.*
448from BlueHeronMetadata.normal_concept@deid norm, (
449  select * from other_meds
450  order by facts desc
451  )
452where rownum < 500
453;
454
455commit;
456
457select case when count(*) = 0 then 1 else 1/0 end ends_with_slash from (
458  select * from rxnorm_terms where substr(c_fullname, length(c_fullname)) != '\'
459  );
460
461select case when cnt = 0 then 1 else 1/0 end no_null_names from (
462  select count(*) cnt from rxnorm_terms where c_name is null
463  );
464
465
466/* For some reason, inserting over a link kept giving me "column ambiguously
467defined".  But, if I create a table first and then insert over the link, it
468worked.
469*/
470
471delete from blueheronmetadata.rxnorm_terms@deid;
472insert into blueheronmetadata.rxnorm_terms@deid (
473  c_hlevel, c_fullname, c_name, c_synonym_cd, c_visualattributes, c_basecode,
474  c_facttablecolumn, c_tablename, c_columnname, c_columndatatype, c_operator, c_dimcode,
475  c_tooltip, m_applied_path, update_date, download_date, import_date, sourcesystem_cd,
476  m_exclusion_cd
477  )
478select
479  c_hlevel, c_fullname, c_name, c_synonym_cd, c_visualattributes, c_basecode,
480  c_facttablecolumn, c_tablename, c_columnname, c_columndatatype,
481  c_operator, c_dimcode, null c_tooltip, m_applied_path, update_date, download_date,
482  import_date, sourcesystem_cd, null m_exclusion_cd
483from rxnorm_terms
484
485union all
486
487/* Exclude some medications from having the modifiers applied.
488*/
489select
490  c_hlevel, c_fullname, c_name, c_synonym_cd, c_visualattributes, c_basecode,
491  c_facttablecolumn, c_tablename, c_columnname, c_columndatatype,
492  c_operator, c_dimcode, c_tooltip, m.m_applied_path || '%' m_applied_path,
493  update_date, download_date, import_date, sourcesystem_cd, 'X' m_exclusion_cd
494from (
495  select c_fullname m_applied_path
496  from med_map_exclude_modifiers@deid ex
497  join rxnorm_terms rxt on rxt.c_basecode = ex.concept_cd
498  ) m, blueheronmetadata.epic_terms@deid
499where c_tablename = 'MODIFIER_DIMENSION'
500and c_fullname like '\Medication\%';
501
502
503/* Make sure there are no duplicates in the full paths of the RxNorm terms table.
504Note that a particular term can be excluded from having a modifier attached even
505when that modifier is applied at a higher level in the hierarchy.  To exclude a
506term, insert another modifier row into the terms table with the same full path
507but with applied path set to the term to be excluded and the exclusion code set
508to 'X'.  So, duplicate rows are acceptable if they are used in this way to
509exclude terms from modifiers.
510*/
511select case when count(*) > 0 then 1/0 else 1 end dup_concepts from(
512  select count(*), c_fullname, m_exclusion_cd, m_applied_path
513  from blueheronmetadata.rxnorm_terms@deid
514  group by c_fullname , m_exclusion_cd, m_applied_path
515  having count(*) > 1 order by count(*) desc
516);
517
518/* To help generate a curated data file of medications that didn't map to RxNorm,
519use this query to map medications with  similar names.  Note that first load
520/curated_data/med_map_like_name.csv which is a manually curated list of medication
521names with wild cards like (%CEFEPIME%).
522*/
523/*
524with
525like_to_va_name as (
526  select distinct sdfc.va_name, sdfc.va_rxaui, sdfc.sdf_rxaui, sdfc.sdf_name, ln.like_name
527  from clarity_children cc
528  join med_map_like_name@deid ln on upper(cc.clarity_name) like ln.like_name
529  join sdf_children sdfc on sdfc.sdf_rxaui = cc.sdf_rxaui
530  order by sdfc.va_name
531  )
532select
533  um.clarity_medication_id, um.clarity_name, um.facts, um.patients,
534  lva.va_name, lva.va_rxaui, lva.sdf_rxaui, lva.sdf_name
535from unmapped_meds um
536join like_to_va_name lva on um.clarity_name like lva.like_name
537order by facts desc --for eyeballing highest fact counts first
538;
539*/
540
541/* Suggested mappings for unmapped meds based on mapped meds with the same
542therapeutic and pharmaceutical classes according to clarity */
543/*
544with
545unmapped_classes as (
546  select um.*, cmed.thera_class_c, cmed.pharm_class_c, cmed.pharm_subclass_c
547  from unmapped_meds um
548  join clarity.clarity_medication cmed on cmed.medication_id = um.clarity_medication_id
549  ),
550mapped_classes as (
551  select mm.*, cmed.name, cmed.thera_class_c, cmed.pharm_class_c, cmed.pharm_subclass_c
552  from mapped_meds mm
553  join clarity.clarity_medication cmed on cmed.medication_id = mm.med_id
554  ),
555same_classes as (
556  select
557    min(mc.med_id) mapped_id,
558    uc.clarity_medication_id unmapped_med_id, uc.clarity_name unmapped_name,
559    uc.facts, uc.patients, thr.name thera_class, phr.name pharm_class,
560    phrs.name pharm_subclass
561  from unmapped_classes uc
562  join mapped_classes mc on mc.thera_class_c = uc.thera_class_c
563    and mc.pharm_class_c = uc.pharm_class_c
564    and mc.pharm_subclass_c = uc.pharm_subclass_c
565  join clarity.zc_thera_class thr on thr.thera_class_c = mc.thera_class_c
566  join clarity.zc_pharm_class phr on phr.pharm_class_c = mc.pharm_class_c
567  join clarity.zc_pharm_subclass phrs on phrs.pharm_subclass_c = mc.pharm_subclass_c
568  group by
569    uc.clarity_medication_id, uc.clarity_name, uc.facts, uc.patients, thr.name,
570    phr.name, phrs.name
571  )
572select
573  unmapped_med_id clarity_medication_id, unmapped_name clarity_name, facts,
574  patients, suggested_va va_name, suggested_va_aui va_rxaui, null sdf_rxaui,
575  null sdf_name
576from (
577  select -- Subquery good for eyeballing
578    cmed.name mapped_name, sc.*, sdfc.va_name suggested_va, sdfc.va_rxaui suggested_va_aui,
579    sdfc.sdf_name suggested_sdf, sdfc.sdf_rxaui suggested_sdf_aui
580  from same_classes sc
581  join clarity_children cc on cc.clarity_med_id = sc.mapped_id
582  join sdf_children sdfc on sdfc.sdf_rxaui = cc.sdf_rxaui
583  join clarity.clarity_medication cmed on cmed.medication_id = sc.mapped_id
584  order by facts desc
585  )
586
587
588select rxt.*, cbc.facts, cbc.patients from rxnorm_terms rxt
589left join blueheronmetadata.counts_by_concept@deid cbc on cbc.concept_cd = rxt.c_basecode
590
591union all
592
593select
594  c_hlevel, c_fullname, c_name, c_visualattributes, c_basecode,
595  c_dimcode, sysdate update_date, sysdate download_date,
596  sysdate import_date, sourcesystem_cd, norm.*, 0 facts, 0 patients
597from I2B2METADATA2.I2B2@deid m, BlueHeronMetadata.normal_concept@deid norm
598where m.c_name = 'Medications'
599;
600*/
601 
602/* Just a general helper to show unmapped meds by fact count.
603*/
604-- select * from unmapped_meds order by facts desc;
605
606--Drop temporary tables.
607drop table clarity_med_id_to_rxcui;
608drop table rxnorm_terms;
609drop table clarity_children;
610drop table sdf_children;
Note: See TracBrowser for help on using the repository browser.