Drug Queries‎ > ‎

D23: Find drugs by class and route of administration

This query is designed to return a list of drug concept IDs that belong to a drug class and require a certain route of administration. For example, it can be used to find all steroid drugs used intravaginally. The query ties together:
  • Concept ancestor data to link drug concepts to therapeutic class 
  • RxNorm concept relationship 4 - ‘Has dose form (RxNorm) 
  • Dose form to route of administration list
The results are combined to present a list of drugs from a specific therapeutic class with a specific route of administration. Permissible routes are:
  • Inhaled
  • Intrathecal
  • Nasal
  • Ophthalmic
  • Oral
  • Unknown (cannot be defined from the dose form)
  • Otic
  • Parenteral
  • Rectal
  • Topical
  • Urethral
  • Vaginal

Input:
 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  'vaginal'  Yes Route of administration string.
 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,
         (
           SELECT 19095898 AS formid, 'Inhaled'     AS route FROM dual UNION
           SELECT 19126918 AS formid, 'Inhaled'     AS route FROM dual UNION
           SELECT 19082258 AS formid, 'Inhaled'     AS route FROM dual UNION
           SELECT 19082259 AS formid, 'Inhaled'     AS route FROM dual UNION
           SELECT 19126919 AS formid, 'Inhaled'     AS route FROM dual UNION
           SELECT 19127579 AS formid, 'Inhaled'     AS route FROM dual UNION
           SELECT 19018195 AS formid, 'Inhaled'     AS route FROM dual UNION
           SELECT 19082260 AS formid, 'Intrathecal' AS route FROM dual UNION
           SELECT 19011167 AS formid, 'Nasal'       AS route FROM dual UNION
           SELECT 19082165 AS formid, 'Nasal'       AS route FROM dual UNION
           SELECT 19082163 AS formid, 'Nasal'       AS route FROM dual UNION
           SELECT 19082164 AS formid, 'Nasal'       AS route FROM dual UNION
           SELECT 19095977 AS formid, 'Nasal'       AS route FROM dual UNION
           SELECT 19082161 AS formid, 'Nasal'       AS route FROM dual UNION
           SELECT 19129634 AS formid, 'Ophthalmic'  AS route FROM dual UNION
           SELECT 19135925 AS formid, 'Ophthalmic'  AS route FROM dual UNION
           SELECT 19082167 AS formid, 'Ophthalmic'  AS route FROM dual UNION
           SELECT 19082166 AS formid, 'Ophthalmic'  AS route FROM dual UNION
           SELECT 19059413 AS formid, 'Ophthalmic'  AS route FROM dual UNION
           SELECT 19082576 AS formid, 'Ophthalmic'  AS route FROM dual UNION
           SELECT 19082573 AS formid, 'Oral'        AS route FROM dual UNION
           SELECT 19082170 AS formid, 'Oral'        AS route FROM dual UNION
           SELECT 19082168 AS formid, 'Oral'        AS route FROM dual UNION
           SELECT 19082079 AS formid, 'Oral'        AS route FROM dual UNION
           SELECT 19082077 AS formid, 'Oral'        AS route FROM dual UNION
           SELECT 19082191 AS formid, 'Oral'        AS route FROM dual UNION
           SELECT 19082223 AS formid, 'Oral'        AS route FROM dual UNION
           SELECT 19135866 AS formid, 'Oral'        AS route FROM dual UNION
           SELECT 19082048 AS formid, 'Oral'        AS route FROM dual UNION
           SELECT 19001949 AS formid, 'Oral'        AS route FROM dual UNION
           SELECT 19082253 AS formid, 'Oral'        AS route FROM dual UNION
           SELECT 19018708 AS formid, 'Oral'        AS route FROM dual UNION
           SELECT 19082076 AS formid, 'Oral'        AS route FROM dual UNION
           SELECT 19082285 AS formid, 'Oral'        AS route FROM dual UNION
           SELECT 19082255 AS formid, 'Oral'        AS route FROM dual UNION
           SELECT 19082080 AS formid, 'Oral'        AS route FROM dual UNION
           SELECT 19103220 AS formid, 'Oral'        AS route FROM dual UNION
           SELECT 19095916 AS formid, 'Oral'        AS route FROM dual UNION
           SELECT 19082050 AS formid, 'Oral'        AS route FROM dual UNION
           SELECT 40001732 AS formid, 'Oral'        AS route FROM dual UNION
           SELECT 19082256 AS formid, 'Oral'        AS route FROM dual UNION
           SELECT 19095976 AS formid, 'Oral'        AS route FROM dual UNION
           SELECT 19095911 AS formid, 'Oral'        AS route FROM dual UNION
           SELECT 19126316 AS formid, 'Oral'        AS route FROM dual UNION
           SELECT 19082281 AS formid, 'Oral'        AS route FROM dual UNION
           SELECT 19082257 AS formid, 'Oral'        AS route FROM dual UNION
           SELECT 19095918 AS formid, 'Oral'        AS route FROM dual UNION
           SELECT 40175589 AS formid, 'Oral'        AS route FROM dual UNION
           SELECT 19082169 AS formid, 'Oral'        AS route FROM dual UNION
           SELECT 40227830 AS formid, 'Oral'        AS route FROM dual UNION
           SELECT 19082074 AS formid, 'Oral'        AS route FROM dual UNION
           SELECT 19082078 AS formid, 'Oral'        AS route FROM dual UNION
           SELECT 40166959 AS formid, 'Oral'        AS route FROM dual UNION
           SELECT 19095971 AS formid, 'Oral'        AS route FROM dual UNION
           SELECT 19082075 AS formid, 'Oral'        AS route FROM dual UNION
           SELECT 19135868 AS formid, 'Oral'        AS route FROM dual UNION
           SELECT 40164192 AS formid, 'Oral'        AS route FROM dual UNION
           SELECT 19021887 AS formid, 'Oral'        AS route FROM dual UNION
           SELECT 19082675 AS formid, 'Oral'        AS route FROM dual UNION
           SELECT 19001943 AS formid, 'Oral'        AS route FROM dual UNION
           SELECT 19127776 AS formid, 'Other'       AS route FROM dual UNION
           SELECT 19129139 AS formid, 'Other'       AS route FROM dual UNION
           SELECT 19082651 AS formid, 'Other'       AS route FROM dual UNION
           SELECT 19102296 AS formid, 'Other'       AS route FROM dual UNION
           SELECT 19082251 AS formid, 'Other'       AS route FROM dual UNION
           SELECT 19082652 AS formid, 'Other'       AS route FROM dual UNION
           SELECT 19135790 AS formid, 'Other'       AS route FROM dual UNION
           SELECT 19111148 AS formid, 'Other'       AS route FROM dual UNION
           SELECT 19111276 AS formid, 'Other'       AS route FROM dual UNION
           SELECT 19082254 AS formid, 'Other'       AS route FROM dual UNION
           SELECT 19082628 AS formid, 'Other'       AS route FROM dual UNION
           SELECT 19130329 AS formid, 'Other'       AS route FROM dual UNION
           SELECT 19001144 AS formid, 'Other'       AS route FROM dual UNION
           SELECT 19135843 AS formid, 'Other'       AS route FROM dual UNION
           SELECT 19082101 AS formid, 'Other'       AS route FROM dual UNION
           SELECT 19082630 AS formid, 'Other'       AS route FROM dual UNION
           SELECT 19082195 AS formid, 'Otic'        AS route FROM dual UNION
           SELECT 19082196 AS formid, 'Otic'        AS route FROM dual UNION
           SELECT 19082194 AS formid, 'Otic'        AS route FROM dual UNION
           SELECT 19082193 AS formid, 'Otic'        AS route FROM dual UNION
           SELECT 19082103 AS formid, 'Parenteral'  AS route FROM dual UNION
           SELECT 19126920 AS formid, 'Parenteral'  AS route FROM dual UNION
           SELECT 19082104 AS formid, 'Parenteral'  AS route FROM dual UNION
           SELECT 19082071 AS formid, 'Parenteral'  AS route FROM dual UNION
           SELECT 19082073 AS formid, 'Parenteral'  AS route FROM dual UNION
           SELECT 19082105 AS formid, 'Parenteral'  AS route FROM dual UNION
           SELECT 40033316 AS formid, 'Parenteral'  AS route FROM dual UNION
           SELECT 19082252 AS formid, 'Parenteral'  AS route FROM dual UNION
           SELECT 19082072 AS formid, 'Parenteral'  AS route FROM dual UNION
           SELECT 19082049 AS formid, 'Parenteral'  AS route FROM dual UNION
           SELECT 19082106 AS formid, 'Parenteral'  AS route FROM dual UNION
           SELECT 19082229 AS formid, 'Parenteral'  AS route FROM dual UNION
           SELECT 19082701 AS formid, 'Parenteral'  AS route FROM dual UNION
           SELECT 19082283 AS formid, 'Rectal'      AS route FROM dual UNION
           SELECT 19082200 AS formid, 'Rectal'      AS route FROM dual UNION
           SELECT 19082627 AS formid, 'Rectal'      AS route FROM dual UNION
           SELECT 19082197 AS formid, 'Rectal'      AS route FROM dual UNION
           SELECT 19082282 AS formid, 'Rectal'      AS route FROM dual UNION
           SELECT 19124968 AS formid, 'Rectal'      AS route FROM dual UNION
           SELECT 19095917 AS formid, 'Rectal'      AS route FROM dual UNION
           SELECT 19082574 AS formid, 'Rectal'      AS route FROM dual UNION
           SELECT 19082162 AS formid, 'Rectal'      AS route FROM dual UNION
           SELECT 19082221 AS formid, 'Rectal'      AS route FROM dual UNION
           SELECT 19082199 AS formid, 'Rectal'      AS route FROM dual UNION
           SELECT 19000942 AS formid, 'Rectal'      AS route FROM dual UNION
           SELECT 19082198 AS formid, 'Rectal'      AS route FROM dual UNION
           SELECT 19082224 AS formid, 'Topical'     AS route FROM dual UNION
           SELECT 19082228 AS formid, 'Topical'     AS route FROM dual UNION
           SELECT 19082227 AS formid, 'Topical'     AS route FROM dual UNION
           SELECT 19095973 AS formid, 'Topical'     AS route FROM dual UNION
           SELECT 19082225 AS formid, 'Topical'     AS route FROM dual UNION
           SELECT 19095912 AS formid, 'Topical'     AS route FROM dual UNION
           SELECT 19008697 AS formid, 'Topical'     AS route FROM dual UNION
           SELECT 19082109 AS formid, 'Topical'     AS route FROM dual UNION
           SELECT 19130307 AS formid, 'Topical'     AS route FROM dual UNION
           SELECT 19095972 AS formid, 'Topical'     AS route FROM dual UNION
           SELECT 19082286 AS formid, 'Topical'     AS route FROM dual UNION
           SELECT 19126590 AS formid, 'Topical'     AS route FROM dual UNION
           SELECT 19009068 AS formid, 'Topical'     AS route FROM dual UNION
           SELECT 19016586 AS formid, 'Topical'     AS route FROM dual UNION
           SELECT 19082110 AS formid, 'Topical'     AS route FROM dual UNION
           SELECT 19082108 AS formid, 'Topical'     AS route FROM dual UNION
           SELECT 19102295 AS formid, 'Topical'     AS route FROM dual UNION
           SELECT 19095900 AS formid, 'Topical'     AS route FROM dual UNION
           SELECT 19082226 AS formid, 'Topical'     AS route FROM dual UNION
           SELECT 19057400 AS formid, 'Topical'     AS route FROM dual UNION
           SELECT 19112648 AS formid, 'Topical'     AS route FROM dual UNION
           SELECT 19082222 AS formid, 'Topical'     AS route FROM dual UNION
           SELECT 19095975 AS formid, 'Topical'     AS route FROM dual UNION
           SELECT 40227748 AS formid, 'Topical'     AS route FROM dual UNION
           SELECT 19135439 AS formid, 'Topical'     AS route FROM dual UNION
           SELECT 19135438 AS formid, 'Topical'     AS route FROM dual UNION
           SELECT 19135440 AS formid, 'Topical'     AS route FROM dual UNION
           SELECT 19135446 AS formid, 'Topical'     AS route FROM dual UNION
           SELECT 19082107 AS formid, 'Topical'     AS route FROM dual UNION
           SELECT 19082575 AS formid, 'urethral'    AS route FROM dual UNION
           SELECT 19095974 AS formid, 'urethral'    AS route FROM dual UNION
           SELECT 19010880 AS formid, 'Vaginal'     AS route FROM dual UNION
           SELECT 19010962 AS formid, 'Vaginal'     AS route FROM dual UNION
           SELECT 19010878 AS formid, 'Vaginal'     AS route FROM dual UNION
           SELECT 19093368 AS formid, 'Vaginal'     AS route FROM dual UNION
           SELECT 19010879 AS formid, 'Vaginal'     AS route FROM dual UNION
           SELECT 40167393 AS formid, 'Vaginal'     AS route FROM dual UNION
           SELECT 19135437 AS formid, 'Vaginal'     AS route FROM dual UNION
           SELECT 19082287 AS formid, 'Vaginal'     AS route FROM dual) routead
 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 F.concept_id            = routead.formid
   AND INSTR(LOWER(REPLACE(REPLACE(routead.route, ' ', ''), '-', '')),
             LOWER(REPLACE(REPLACE('vaginal' , ' ', ''), '-', ''))) > 0
   AND sysdate BETWEEN CRF.valid_start_date AND CRF.valid_end_date
Output:
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  40230686
 Drug_Name  hydrocortisone acetate 10 MG/ML Vaginal Cream
 Drug_Concept_Code  1039349
Comments