This query is designed to extract codes from a non-standard drug vocabulary that belong to a therapeutic class. The query accepts a therapeutic class concept ID and the vocabualry ID of the desired source vocabulary as input and returns all codes that are included under that class and that belong to a source vocabulary. This query could be used to derive e.g. all NDC codes that belong to a certain drug 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
Drug source codes could be from one of the following:
- ICD-9-Procedure, vocabulary ID = 3
- CPT-4, vocabulary ID = 4
- NDC, vocabulary ID = 9
- Medi-Span GPI, vocabulary ID = 10
- Multum, vocabulary ID = 16
- FDB Multilex, vocabulary ID = 22
- VA Product, vocabulary ID = 28
- NLM MeSH, vocabulary ID = 46
- FDA SPL, vocabulary ID = 50
- FDB Genseqno, vocabulary ID = 53
Input:
| Parameter |
Example |
Mandatory |
Notes |
| Therapeutic Class Concept ID |
21506108 |
Yes |
Concept ID for ‘ACE Inhibitors and ACE Inhibitor Combinations’ |
| Source Vocabulary ID |
9 |
Yes |
One of the above drug vocabulary ID's |
| 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 M.source_code,
M.source_vocabulary_id,
V.vocabulary_name as source_vocabulary_name,
M.source_code_description
FROM vocabulary.source_to_concept_map M,
vocabulary.concept_ancestor CA,
vocabulary.vocabulary V
WHERE CA.ancestor_concept_id = 21506108
AND CA.descendant_concept_id = M.target_concept_id
AND M.source_vocabulary_id = 9
AND M.mapping_type = 'DRUG'
AND V.vocabulary_id = M.source_vocabulary_id
AND sysdate BETWEEN M.valid_start_date AND M.valid_end_date
Output:
Output field list:
| Field |
Description |
| Source_Code |
Source code of drug in non-standard vocabulary (e.g. NDC code, FDA SPL number etc.) |
| Source_Vocabulary_ID |
Vocabulary ID of source vocabulary |
| Source_Vocabulary_Name |
Vocabulary name of source vocabulary |
| Source_Code_Description |
Description of source code |
Sample output record: (All standard Concept IDs in the therapeutic class)
| Field |
Value |
| Source_Code |
00003033805 |
| Source_Vocabulary_ID |
9 |
| Source_Vocabulary_Name |
NDC |
| Source_Code_Description |
Captopril 25 MG / Hydrochlorothiazide 15 MG Oral Tablet |
|
|
