Drug Queries‎ > ‎

D22: Find drugs by class and dose form

This query is designed to return a list of drug concept IDs that belong to a drug class and are of a certain dose form. The query ties together:
  • Concept ancestor data to link drug concepts to therapeutic class 
  • RxNorm concept relationship 4 - ‘Has dose form (RxNorm) 
The results are combined to present a list of drugs from a specific therapeutic class with a specific dose form.

 Parameter  Example  Mandatory  Notes
Therapeutic class Concept ID  4318008  Yes Concept ID for mechanism of action “Corticosteroid Hormone Receptor Agonists”. Valid drug classes can be obtained using query D02.
 Dose Form String  'Nasal spray'  Yes Dose form string. Valid dose forms can be obtained using query D19.
 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 of a specific therapeutic class and specific dose form 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_code    drug_concept_code
FROM    vocabulary.concept                      C,
        vocabulary.concept_ancestor             CA,
        vocabulary.concept_relationship         CRF,
        vocabulary.concept                      F
WHERE  CA.ancestor_concept_id = 4318008 
  AND  C.concept_id           = CA.descendant_concept_id
  AND  C.vocabulary_id        = 8
  AND  C.concept_level        = 1
  AND  CRF.concept_id_1       = C.concept_id
  AND  CRF.relationship_ID    = 4
  AND  CRF.concept_id_2       = F.concept_id
  AND  INSTR(LOWER(REPLACE(REPLACE(F.concept_name, ' ', ''), '-', '')),
          LOWER(REPLACE(REPLACE('Nasal spray' , ' ', ''), '-', ''))) > 0
  AND sysdate BETWEEN CRF.valid_start_date AND CRF.valid_end_date
Output field list:
 Field  Description
 Drug_Concept_ID  Concept ID of drug with specified therapeutic class and dose form
 Drug_Name  Name of drug with specified therapeutic class and dose form
 Drug_Concept_Code  Source code of drug

Sample output record:
 Field  Value
 Drug_Concept_ID  904131
 Drug_Name  Triamcinolone 0.055 MG/ACTUAT Nasal Spray
 Drug_Concept_Code  245785