This query is designed to return the therapeutic classes that associated with a drug. The query accepts a standard drug concept ID (e.g. as identified from query G03) as the input. The drug concept can be a clinical or branded drug or pack (concept_level=1), or an ingredient (concept_level=2). The query returns one or more therapeutic classes associated with the drug based on the following classifications.).
- Enhanced Therapeutic Classification (ETC)
- Anatomical Therapeutic Chemical classification (ATC)
- NDF-RT Mechanism of Action (MoA)
- NDF-RT Physiologic effect
- NDF-RT Chemical structure
- VA Class
By default, the query returns therapeutic classes based on all the classification systems listed above. Additional clauses can be added to restrict the query to a single classification system.
Input:
| Parameter |
Example |
Mandatory |
Notes |
| Drug Concept ID |
1545999 |
Yes |
Concept Identifier from RxNorm for ‘atorvastatin 20 MG Oral Tablet [Lipitor]’ |
| 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 therapeutic classes associated with a drug concept. The input parameters are highlighted in blue.
SELECT
A.concept_id Class_Concept_Id,
A.concept_name Class_Name,
A.concept_code Class_Code,
A.concept_class Classification,
A.vocabulary_id Class_vocabulary_id,
V.vocabulary_name Class_vocabulary_name,
CA.min_levels_of_separation Levels_of_Separation
FROM vocabulary.concept_ancestor CA,
vocabulary.concept A,
vocabulary.concept D,
vocabulary.vocabulary V
WHERE CA.descendant_concept_id = D.concept_id
AND CA.ancestor_concept_id = A.concept_id
AND A.vocabulary_id IN (7, 20, 21, 32) -- NDF-RT, FDB ETC, WHO ATC, VA Class
AND lower(A.concept_class) IN (
'anatomical therapeutic chemical classification',
'enhanced therapeutic classification',
'mechanism of action',
'physiologic effect',
'chemical structure',
'va class'
)
AND A.vocabulary_id = V.vocabulary_id
AND D.concept_id = 1545999
AND sysdate BETWEEN A.valid_start_date AND A.valid_end_date
Output:
Output field list:
| Field |
Description |
| Class_Concept_ID |
Concept ID of the therapeutic class |
| Class_Name |
Name of the therapeutic class |
| Class_Code |
Concept Code of therapeutic class |
| Classification |
Concept class of therapeutic class |
| Class_Vocabulary_ID |
Vocabulary the therapeutic class is derived from, expressed as vocabulary ID |
| Class_Vocabulary_Name |
Name of the vocabulary the therapeutic class is derived from |
| Levels_of_Separation |
Levels of separation between the drug concept and the therapeutic class. Important for hierarchic classification systems to identify classes and subclasses for the drug. |
Sample output record:
| Field |
Value |
| Class_Concept_ID |
21500263 |
| Class_Name |
Antihyperlipidemics |
| Class_Code |
263 |
| Classification |
Enhanced Therapeutic Classification |
| Class_Vocabulary_ID |
20 |
| Class_Vocabulary_Name |
ETC |
| Levels_of_Separation |
2 |
|
|
