This query enables extraction of all procedures that are used for drug administration (such as injectables) and the corresponding drugs being administered. The query does not include any filters for individual drugs or individual procedures and returns a list of all such combinations in the vocabulary.
Input:
Parameter |
Example |
Mandatory |
Notes |
As of date |
Sysdate |
No |
Valid record as of specific date. Current date – sysdate is a default |
Sample query run:
The following is a sample run of the query to extract all procedures used for drug administration.
SELECT SC.source_CODE Procedure_code,
SC.source_code_description Procedure_name,
SC.source_vocabulary_id Procedure_vocabulary_id,
VS.vocabulary_name Procedure_vocabulary_name,
NVL(CD2.concept_id, CD.concept_id) Drug_concept_id,
NVL(CD2.concept_name, CD.concept_name) Drug_name,
NVL(CD2.concept_code, CD.concept_code) Drug_concept_code,
NVL(CD2.concept_class, CD.concept_class) Drug_concept_class,
NVL(CD2.vocabulary_id, CD.vocabulary_id) Drug_vocabulary_id
FROM vocabulary.source_to_concept_map SC
INNER JOIN vocabulary.vocabulary VS ON SC.source_vocabulary_id = VS.vocabulary_id
INNER JOIN vocabulary.concept CD ON SC.target_concept_id = CD.concept_id
AND sysdate BETWEEN CD.valid_start_date AND CD.valid_end_date
LEFT OUTER JOIN vocabulary.concept_relationship CR ON CD.concept_id = CR.concept_id_1
AND CR.relationship_id = 102
LEFT OUTER JOIN vocabulary.concept CD2 ON CR.concept_id_2 = CD2.concept_id
WHERE SC.source_vocabulary_id IN (3,4,5)
AND lower(SC.mapping_type) = 'procedure drug'
Output:
Output field list:
Field |
Description |
Procedure_code |
Mapped source code of procedure used for drug administration |
Procedure_name |
Name of procedure used for drug administration |
Procedure_vocabulary_id |
Source vocabulary the procedure code is derived from, expressed as vocabulary ID |
Procedure_vocabulary_name |
Name of the vocabulary the procedure code is derived from |
Drug_concept_id |
Concept ID of drug being administered by the procedure |
Drug_name |
Name of drug being administered by the procedure |
Drug_concept_code |
Concept code of drug being administered by the procedure |
Drug_concept_class |
Concept class of drug being administered by the procedure |
Drug_vocabulary_id |
Vocabulary ID of the vocabulary from which the disease/condition concept is derived from |
Sample output record:
Field |
Value |
Procedure_code |
A9505 |
Procedure_name |
Thallous 1 MCI |
Procedure_vocabulary_id |
5 |
Procedure_vocabulary_name |
HCPCS |
Drug_concept_id |
19135941 |
Drug_name |
Thallous chloride Tl201 |
Drug_concept_code |
91588 |
Drug_concept_class |
Ingredient |
Drug_vocabulary_id |
8 |
|
|