Drug Queries‎ > ‎

D15: Find drugs for an indication provided as condition concepts

This query provides all clinical/branded drugs that are indicated for a certain indication. Indications have to be provided as SNOMED-CT concept (vocabulary_id=1).

 Parameter  Example  Mandatory  Notes
 Indication Concept ID  253954  Yes SNOMED-CT indication concept ID
 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 drugs associated with SNOMED-CT indication. The necessary parameter substitution is highlighted in blue.

       drug.concept_id      as drug_concept_id,
       drug.concept_name    as drug_concept_name,
       drug.concept_code    as drug_concept_code
FROM   vocabulary.concept              drug,
       vocabulary.concept_ancestor     snomed,
       vocabulary.concept_ancestor     ind,
       vocabulary.concept_relationship r
WHERE  snomed.ancestor_concept_id   = 253954 
AND    snomed.descendant_concept_id = r.concept_id_1
AND    concept_id_2                 = ind.ancestor_concept_id
AND    r.relationship_id in (247, 248)
AND    ind.descendant_concept_id    = drug.concept_id
AND    drug.concept_level           = 1
AND    drug.vocabulary_id           = 8
AND    sysdate BETWEEN drug.valid_start_date AND drug.valid_end_date
Output field list:
 Field  Description
 Drug_Concept_ID  Concept ID of the drug
 Drug_Concept_Name  Name of the drug
 Drug_Concept_Code  Concept code of the drug

Sample output record:
 Field  Value
 Drug_Concept_ID  19073074
 Drug_Concept_Name  Aminosalicylic Acid 500 MG Oral Tablet
 Drug_Concept_Code  308122