Drug Queries‎ > ‎

D09: Find drugs by drug class

This query is designed to extract all drugs that belong to a therapeutic class. The query accepts a therapeutic class concept ID as the input and returns all drugs that are included under that 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

Input:
 Parameter  Example  Mandatory  Notes
 Therapeutic Class Concept ID  21506108  Yes Concept ID for ‘ACE Inhibitors and ACE Inhibitor Combinations’
 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  C.concept_id      drug_concept_id,
        C.concept_name   drug_concept_name,
        C.concept_class  drug_concept_class,
        C.concept_code   drug_concept_code
FROM    vocabulary.concept          C,
         vocabulary.concept_ancestor CA
 WHERE   CA.ancestor_concept_id = 21506108 
   AND  C.concept_id            = CA.descendant_concept_id
   AND  C.vocabulary_id         = 8
   AND  C.concept_level         = 1
   AND sysdate BETWEEN C.valid_start_date AND C.valid_end_date
 Output:
Output field list:
 Field  Description
 Drug_Concept_ID  Concept ID of drug included in therapeutic class
 Drug_Concept_Name  Name of drug concept included in therapeutic class
 Drug_Concept_Class  Concept class of drug concept included in therapeutic class
 Drug_Concept_Code  RxNorm source code of drug concept

Sample output record: (All standard Concept IDs in the therapeutic class)
 Field  Value
 Drug_Concept_ID  1308221
 Drug_Concept_Name  Lisinopril 40 MG Oral Tablet
 Drug_Concept_Class  Clinical Drug
 Drug_Concept_Code  197884
Comments