Drug Queries‎ > ‎

D11: Find source codes by drug class

This query is designed to extract codes from a non-standard drug vocabulary that belong to a therapeutic class. The query accepts a therapeutic class concept ID and the vocabualry ID of the desired source vocabulary as input and returns all codes that are included under that class and that belong to a source vocabulary. This query could be used to derive e.g. all NDC codes that belong to a certain drug class.
Therapeutic classes could be obtained using query D02 and are derived from one of the following:
  • Enhanced Therapeutic Classification (FDB ETC), VOCABULARY_ID = 20.
  • Anatomical Therapeutic Chemical classification (WHO ATC), VOCABULARY_ID = 21
 NDF-RT Mechanism of Action (MoA), Vocabulary ID = 7, Concept Class = ‘Mechanism of Action’
– NDF-RT Physiologic effect (PE),        Vocabulary ID = 7, Concept Class = ‘Physiologic Effect’
– NDF-RT Chemical Structure,              Vocabulary ID = 7, Concept Class = ‘Chemical Structure’
  • VA Class, Vocabulary ID = 32
Drug source codes could be from one of the following:
  • ICD-9-Procedure,    vocabulary ID = 3 
  • CPT-4,                   vocabulary ID = 4 
  • NDC,                     vocabulary ID = 9 
  • Medi-Span GPI,     vocabulary ID = 10 
  • Multum,                 vocabulary ID = 16 
  • FDB Multilex,         vocabulary ID = 22 
  • VA Product,           vocabulary ID = 28 
  • NLM MeSH,           vocabulary ID = 46 
  • FDA SPL,              vocabulary ID = 50 
  • FDB Genseqno,     vocabulary ID = 53

 Parameter  Example  Mandatory  Notes
 Therapeutic Class Concept ID  21506108  Yes Concept ID for ‘ACE Inhibitors and ACE Inhibitor Combinations’
 Source Vocabulary ID  9  Yes One of the above drug vocabulary ID's
 As of date  Sysdate  No Valid record as of specific date. Current date – sysdate is a default

Sample query run:
The following are sample runs of the query to extract list of drugs that have a given therapeutic class, concept ID for which is entered as input. The input parameters are highlighted in blue.

SELECT  M.source_code,
        V.vocabulary_name         as source_vocabulary_name,
 FROM    vocabulary.source_to_concept_map M,
         vocabulary.concept_ancestor      CA,
         vocabulary.vocabulary            V
 WHERE  CA.ancestor_concept_id   = 21506108 
   AND  CA.descendant_concept_id = M.target_concept_id
   AND  M.source_vocabulary_id   = 9 
   AND  M.mapping_type           = 'DRUG'
   AND V.vocabulary_id           = M.source_vocabulary_id
   AND sysdate BETWEEN M.valid_start_date AND M.valid_end_date
Output field list:
 Field  Description
 Source_Code  Source code of drug in non-standard vocabulary (e.g. NDC code, FDA SPL number etc.)
 Source_Vocabulary_ID  Vocabulary ID of source vocabulary
 Source_Vocabulary_Name  Vocabulary name of source vocabulary
 Source_Code_Description  Description of source code

Sample output record: (All standard Concept IDs in the therapeutic class)
 Field  Value
 Source_Code  00003033805
 Source_Vocabulary_ID  9
 Source_Vocabulary_Name  NDC
 Source_Code_Description  Captopril 25 MG / Hydrochlorothiazide 15 MG Oral Tablet