Drug Queries‎ > ‎

D16: Find drugs for an indication by indication type

This query provides all drugs that are indicated for a certain condition. In addition, it provides the type of indication: FDA-approved, off-label (both based on FDB indication classes) and may treat and may prevent (both based on NDF-RT). Indications have to be provided as FDB indications (vocabulary_id=19) or NDF-RT (vocabulary_id=7).

Input:
 Parameter  Example  Mandatory  Notes
 Indication Concept ID  4345991  Yes FDB indication concept for 'Vomiting'
 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 an indication. The necessary parameter substitution is highlighted in blue.
SELECT DISTINCT
 drug.concept_id      as drug_concept_id,
 drug.concept_name    as drug_concept_name,
 drug.concept_code    as drug_concept_code,
 rn.relationship_name as indication_type,
 indication_relation.relationship_id
FROM
  vocabulary.concept_relationship indication_relation
INNER JOIN vocabulary.concept_ancestor a
 ON a.ancestor_concept_id=indication_relation.concept_id_2
INNER JOIN vocabulary.concept drug
 ON drug.concept_id=a.descendant_concept_id
INNER JOIN vocabulary.relationship rn
 ON rn.relationship_id=indication_relation.relationship_id
WHERE indication_relation.concept_id_1 = 4345991  
  AND drug.vocabulary_id = 8
  AND drug.concept_level = 1
  AND indication_relation.relationship_id in (21,23,155,157,126,127,240,241,281,282)
  AND sysdate BETWEEN drug.valid_start_date AND drug.valid_end_date
Output:
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
 Indication_Type  One of the FDB, NDF-RT or OMOP inferred indication types
 Relationship_id  Corresponding relationship ID to the Indication Type

Sample output record:
 Field  Value
 Drug_Concept_ID  19019530
 Drug_Concept_Name  Perphenazine 4 MG Oral Tablet
 Drug_Concept_Code  198077
 Indication_Type  Inferred ingredient of (OMOP)
 Relationship_id  281
Comments