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