Drug Queries‎ > ‎

D10: Find ingredient by drug class

This query is designed to extract all ingredients 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

 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 ingredients that belong to a given therapeutic class concept ID as input. The input parameters are highlighted in blue.
SELECT  C.concept_id    ingredient_concept_id,
        C.concept_name  ingredient_concept_name,
        C.concept_class ingredient_concept_class,
        C.concept_code  ingredient_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        = 2
   AND  sysdate BETWEEN C.valid_start_date AND C.valid_end_date
Output field list:
 Field  Description
 Ingredient_Concept_ID  Concept ID of ingredient included in therapeutic class
 Ingredient_Concept_Name  Name of ingredient concept included in therapeutic class
 Ingredient_Concept_Class  Concept class of ingredient concept included in therapeutic class
 Ingredient_Concept_Code  RxNorm source code of ingredient concept

Sample output record: (All standard Concept IDs in the therapeutic class)
 Field  Value
 Ingredient_Concept_ID  1308216
 Ingredient_Concept_Name  Lisinopril
 Ingredient_Concept_Class  Ingredient
 Ingredient_Concept_Code  29046