Drug Queries‎ > ‎

D14: Find drugs for an indication

This query provides all clinical or branded drugs that are indicated for a certain indication. Indications have to be given as FDB indications (vocabulary_id=19) or NDF-RT indications (vocabulary_id=7). Indications can be identified using the generic query G03, or, if at least one drug is known for this indication, query D04.

Input:
 Parameter  Example  Mandatory  Notes
 Indication Concept ID  21000039  Yes FDB 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 an indication. The necessary parameter substitution is highlighted in blue

SELECT
        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 a
 WHERE  a.ancestor_concept_id   = 21000039 
 AND    a.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:
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  1710446
 Drug_Concept_Name  Cycloserine
 Drug_Concept_Code  3007
Comments