This query is designed to extract all drugs that belong to a therapeutic class. The query accepts a therapeutic class concept ID as the input and returns all drugs that are included under that class .
Therapeutic classes could be obtained using query D02 and are derived from one of the following:
- Enhanced Therapeutic Classification (FDB ETC), VOCABULARY_ID = 20
- Anatomical Therapeutic Chemical classification (WHO ATC), VOCABULARY_ID = 21
– NDF-RT Mechanism of Action (MoA), Vocabulary ID = 7, Concept Class = ‘Mechanism of Action’
– NDF-RT Physiologic effect (PE), Vocabulary ID = 7, Concept Class = ‘Physiologic Effect’
– NDF-RT Chemical Structure, Vocabulary ID = 7, Concept Class = ‘Chemical Structure’
- VA Class, Vocabulary ID = 32
Input:
Parameter |
Example |
Mandatory |
Notes |
Therapeutic Class Concept ID |
21506108 |
Yes |
Concept ID for ‘ACE Inhibitors and ACE Inhibitor Combinations’ |
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 list of drugs that have a given therapeutic class, concept ID for which is entered as input. The input parameters are highlighted in blue.
SELECT C.concept_id drug_concept_id,
C.concept_name drug_concept_name,
C.concept_class drug_concept_class,
C.concept_code drug_concept_code
FROM vocabulary.concept C,
vocabulary.concept_ancestor CA
WHERE CA.ancestor_concept_id = 21506108
AND C.concept_id = CA.descendant_concept_id
AND C.vocabulary_id = 8
AND C.concept_level = 1
AND sysdate BETWEEN C.valid_start_date AND C.valid_end_date
Output:
Output field list:
Field |
Description |
Drug_Concept_ID |
Concept ID of drug included in therapeutic class |
Drug_Concept_Name |
Name of drug concept included in therapeutic class |
Drug_Concept_Class |
Concept class of drug concept included in therapeutic class |
Drug_Concept_Code |
RxNorm source code of drug concept |
Sample output record: (All standard Concept IDs in the therapeutic class)
Field |
Value |
Drug_Concept_ID |
1308221 |
Drug_Concept_Name |
Lisinopril 40 MG Oral Tablet |
Drug_Concept_Class |
Clinical Drug |
Drug_Concept_Code |
197884 |
|
|