Drug Queries‎ > ‎

D21: Find route of administration of a drug

This query accepts concept IDs for a drug product (clinical or branded drug or pack) and identifies the route of administration of the dose form. The following routes of administration are defined:
  • Inhaled
  • Intrathecal
  • Nasal
  • Ophthalmic
  • Oral
  • Unknown (cannot be defined from the dose form)
  • Otic
  • Parenteral
  • Rectal
  • Topical
  • Urethral
  • Vaginal

Input:
 Parameter  Example  Mandatory  Notes
 Drug Concept ID   19060647  Yes Must be a level 1 Clinical or Branded Drug or Pack
 As of date  Sysdate  No Valid record as of specific date. Current date – sysdate is a default

Sample query run:
The following is a sample query. The input parameters are highlighted in blue.
SELECT  A.concept_id               drug_concept_id,
        A.concept_name             drug_concept_name,
        A.concept_code             drug_concept_code,
        D.concept_name             dose_form_concept_name,
        (CASE D.concept_id
          WHEN 19095898 THEN 'Inhaled'
          WHEN 19126918 THEN 'Inhaled'
          WHEN 19082258 THEN 'Inhaled'
          WHEN 19082259 THEN 'Inhaled'
          WHEN 19126919 THEN 'Inhaled'
          WHEN 19127579 THEN 'Inhaled'
          WHEN 19018195 THEN 'Inhaled'
          WHEN 19082260 THEN 'Intrathecal'
          WHEN 19011167 THEN 'Nasal'
          WHEN 19082165 THEN 'Nasal'
          WHEN 19082163 THEN 'Nasal'
          WHEN 19082164 THEN 'Nasal'
          WHEN 19095977 THEN 'Nasal'
          WHEN 19082161 THEN 'Nasal'
          WHEN 19129634 THEN 'Ophthalmic'
          WHEN 19135925 THEN 'Ophthalmic'
          WHEN 19082167 THEN 'Ophthalmic'
          WHEN 19082166 THEN 'Ophthalmic'
          WHEN 19059413 THEN 'Ophthalmic'
          WHEN 19082576 THEN 'Ophthalmic'
          WHEN 19082573 THEN 'Oral'
          WHEN 19082170 THEN 'Oral'
          WHEN 19082168 THEN 'Oral'
          WHEN 19082079 THEN 'Oral'
          WHEN 19082077 THEN 'Oral'
          WHEN 19082191 THEN 'Oral'
          WHEN 19082223 THEN 'Oral'
          WHEN 19135866 THEN 'Oral'
          WHEN 19082048 THEN 'Oral'
          WHEN 19001949 THEN 'Oral'
          WHEN 19082253 THEN 'Oral'
          WHEN 19018708 THEN 'Oral'
          WHEN 19082076 THEN 'Oral'
          WHEN 19082285 THEN 'Oral'
          WHEN 19082255 THEN 'Oral'
          WHEN 19082080 THEN 'Oral'
          WHEN 19103220 THEN 'Oral'
          WHEN 19095916 THEN 'Oral'
          WHEN 19082050 THEN 'Oral'
          WHEN 40001732 THEN 'Oral'
          WHEN 19082256 THEN 'Oral'
          WHEN 19095976 THEN 'Oral'
          WHEN 19095911 THEN 'Oral'
          WHEN 19126316 THEN 'Oral'
          WHEN 19082281 THEN 'Oral'
          WHEN 19082257 THEN 'Oral'
          WHEN 19095918 THEN 'Oral'
          WHEN 40175589 THEN 'Oral'
          WHEN 19082169 THEN 'Oral'
          WHEN 40227830 THEN 'Oral'
          WHEN 19082074 THEN 'Oral'
          WHEN 19082078 THEN 'Oral'
          WHEN 40166959 THEN 'Oral'
          WHEN 19095971 THEN 'Oral'
          WHEN 19082075 THEN 'Oral'
          WHEN 19135868 THEN 'Oral'
          WHEN 40164192 THEN 'Oral'
          WHEN 19021887 THEN 'Oral'
          WHEN 19082675 THEN 'Oral'
          WHEN 19001943 THEN 'Oral'
          WHEN 19127776 THEN 'Unknown'
          WHEN 19129139 THEN 'Unknown'
          WHEN 19082651 THEN 'Unknown'
          WHEN 19102296 THEN 'Unknown'
          WHEN 19082251 THEN 'Unknown'
          WHEN 19082652 THEN 'Unknown'
          WHEN 19135790 THEN 'Unknown'
          WHEN 19111148 THEN 'Unknown'
          WHEN 19111276 THEN 'Unknown'
          WHEN 19082254 THEN 'Unknown'
          WHEN 19082628 THEN 'Unknown'
          WHEN 19130329 THEN 'Unknown'
          WHEN 19001144 THEN 'Unknown'
          WHEN 19135843 THEN 'Unknown'
          WHEN 19082101 THEN 'Unknown'
          WHEN 19082630 THEN 'Unknown'
          WHEN 19082195 THEN 'Otic'
          WHEN 19082196 THEN 'Otic'
          WHEN 19082194 THEN 'Otic'
          WHEN 19082193 THEN 'Otic'
          WHEN 19082103 THEN 'Parenteral'
          WHEN 19126920 THEN 'Parenteral'
          WHEN 19082104 THEN 'Parenteral'
          WHEN 19082071 THEN 'Parenteral'
          WHEN 19082073 THEN 'Parenteral'
          WHEN 19082105 THEN 'Parenteral'
          WHEN 40033316 THEN 'Parenteral'
          WHEN 19082252 THEN 'Parenteral'
          WHEN 19082072 THEN 'Parenteral'
          WHEN 19082049 THEN 'Parenteral'
          WHEN 19082106 THEN 'Parenteral'
          WHEN 19082229 THEN 'Parenteral'
          WHEN 19082701 THEN 'Parenteral'
          WHEN 19082283 THEN 'Rectal'
          WHEN 19082200 THEN 'Rectal'
          WHEN 19082627 THEN 'Rectal'
          WHEN 19082197 THEN 'Rectal'
          WHEN 19082282 THEN 'Rectal'
          WHEN 19124968 THEN 'Rectal'
          WHEN 19095917 THEN 'Rectal'
          WHEN 19082574 THEN 'Rectal'
          WHEN 19082162 THEN 'Rectal'
          WHEN 19082221 THEN 'Rectal'
          WHEN 19082199 THEN 'Rectal'
          WHEN 19000942 THEN 'Rectal'
          WHEN 19082198 THEN 'Rectal'
          WHEN 19082224 THEN 'Topical'
          WHEN 19082228 THEN 'Topical'
          WHEN 19082227 THEN 'Topical'
          WHEN 19095973 THEN 'Topical'
          WHEN 19082225 THEN 'Topical'
          WHEN 19095912 THEN 'Topical'
          WHEN 19008697 THEN 'Topical'
          WHEN 19082109 THEN 'Topical'
          WHEN 19130307 THEN 'Topical'
          WHEN 19095972 THEN 'Topical'
          WHEN 19082286 THEN 'Topical'
          WHEN 19126590 THEN 'Topical'
          WHEN 19009068 THEN 'Topical'
          WHEN 19016586 THEN 'Topical'
          WHEN 19082110 THEN 'Topical'
          WHEN 19082108 THEN 'Topical'
          WHEN 19102295 THEN 'Topical'
          WHEN 19095900 THEN 'Topical'
          WHEN 19082226 THEN 'Topical'
          WHEN 19057400 THEN 'Topical'
          WHEN 19112648 THEN 'Topical'
          WHEN 19082222 THEN 'Topical'
          WHEN 19095975 THEN 'Topical'
          WHEN 40227748 THEN 'Topical'
          WHEN 19135439 THEN 'Topical'
          WHEN 19135438 THEN 'Topical'
          WHEN 19135440 THEN 'Topical'
          WHEN 19135446 THEN 'Topical'
          WHEN 19082107 THEN 'Topical'
          WHEN 19082575 THEN 'urethral'
          WHEN 19095974 THEN 'urethral'
          WHEN 19010880 THEN 'Vaginal'
          WHEN 19010962 THEN 'Vaginal'
          WHEN 19010878 THEN 'Vaginal'
          WHEN 19093368 THEN 'Vaginal'
          WHEN 19010879 THEN 'Vaginal'
          WHEN 40167393 THEN 'Vaginal'
          WHEN 19135437 THEN 'Vaginal'
          WHEN 19082287 THEN 'Vaginal'
          END
        ) Route_of_Administration
FROM   vocabulary.concept_relationship    CR,
       vocabulary.concept                 A,
       vocabulary.concept                 D
WHERE  CR.concept_id_1    = 19060647 
AND    CR.relationship_ID = 4
AND    CR.concept_id_1    = A.concept_id
AND    CR.concept_id_2    = D.concept_id
AND sysdate BETWEEN CR.valid_start_date AND CR.valid_end_date
Output:
Output field list:
 Field  Description
 Drug_Concept_ID  Concept ID of drug entered with specified dose form
 Drug_Name  Name of drug with specified dose form
 Drug_Concept_Code  Concept code of the dose form
 Dose_Form_Concept_name  Name of the dose form
 Route_Of_Administration  Derived route of administration for the drug

Sample output record:
 Field  Value
 Drug_Concept_ID  19060647
 Drug_Name  Budesonide 0.2 MG/ACTUAT Inhalant Powder
 Drug_Concept_Code  247047
 Dose_Form_Concept_name  Inhalant Powder
 Route_Of_Administration  Inhaled
Comments