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 |
|
|
