Drug Queries‎ > ‎

D08: Find drug classes for a drug or ingredient

This query is designed to return the therapeutic classes that associated with a drug. The query accepts a standard drug concept ID (e.g. as identified from query G03) as the input. The drug concept can be a clinical or branded drug or pack (concept_level=1), or an ingredient (concept_level=2). The query returns one or more therapeutic classes associated with the drug based on the following classifications.).
  • Enhanced Therapeutic Classification (ETC)
  • Anatomical Therapeutic Chemical classification (ATC)
  • NDF-RT Mechanism of Action (MoA)
  • NDF-RT Physiologic effect
  • NDF-RT Chemical structure
  • VA Class
By default, the query returns therapeutic classes based on all the classification systems listed above. Additional clauses can be added to restrict the query to a single classification system.

 Parameter  Example  Mandatory  Notes
  Drug Concept ID  1545999  Yes Concept Identifier from RxNorm for ‘atorvastatin 20 MG Oral Tablet [Lipitor]’
 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 all therapeutic classes associated with a drug concept. The input parameters are highlighted in blue.
 A.concept_id                 Class_Concept_Id,
 A.concept_name               Class_Name,
 A.concept_code               Class_Code,
 A.concept_class              Classification,
 A.vocabulary_id              Class_vocabulary_id,
 V.vocabulary_name            Class_vocabulary_name,
 CA.min_levels_of_separation  Levels_of_Separation
FROM vocabulary.concept_ancestor   CA,
 vocabulary.concept                A,
 vocabulary.concept                D,
 vocabulary.vocabulary             V
WHERE  CA.descendant_concept_id = D.concept_id
AND    CA.ancestor_concept_id = A.concept_id
AND    A.vocabulary_id IN (7, 20, 21, 32)   -- NDF-RT, FDB ETC, WHO ATC, VA Class
AND    lower(A.concept_class) IN (
 'anatomical therapeutic chemical classification',
 'enhanced therapeutic classification',
 'mechanism of action',
 'physiologic effect',
 'chemical structure',
 'va class'
AND    A.vocabulary_id = V.vocabulary_id
AND    D.concept_id = 1545999 
AND    sysdate BETWEEN A.valid_start_date AND A.valid_end_date
Output field list:
 Field  Description
 Class_Concept_ID  Concept ID of the therapeutic class
 Class_Name  Name of the therapeutic class
 Class_Code  Concept Code of therapeutic class
 Classification  Concept class of therapeutic class
 Class_Vocabulary_ID  Vocabulary the therapeutic class is derived from, expressed as vocabulary ID
 Class_Vocabulary_Name  Name of the vocabulary the therapeutic class is derived from
 Levels_of_Separation  Levels of separation between the drug concept and the therapeutic class. Important for hierarchic classification systems to identify classes and subclasses for the drug.

Sample output record:
 Field  Value
 Class_Concept_ID  21500263
 Class_Name  Antihyperlipidemics
 Class_Code  263
 Classification  Enhanced Therapeutic Classification
 Class_Vocabulary_ID  20
 Class_Vocabulary_Name  ETC
 Levels_of_Separation  2