This is the lookup for obtaining drug concept details associated with a concept identifier. This query is intended as a tool for quick reference for the name, class, level and source vocabulary details associated with a concept identifier.
This query is equivalent to G01, but if the concept is not in the drug domain the query still returns the concept details with the Is_Drug_Concept_Flag field set to ‘No’.
Input:
Parameter |
Example |
Mandatory |
Notes |
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 is a sample run of the query to run a search for drug concept ID of 1545999. The input parameters are highlighted in blue.
SELECT C.concept_id Drug_concept_id,
C.concept_name Drug_concept_name,
C.concept_code Drug_concept_code,
C.concept_class Drug_concept_class,
C.concept_level Drug_concept_level,
C.vocabulary_id Drug_concept_vocab_id,
V.vocabulary_name Drug_concept_vocab_code,
(
CASE C.vocabulary_id
WHEN 8 THEN
CASE lower(C.concept_class)
WHEN 'clinical drug' THEN 'Yes'
WHEN 'branded drug' THEN 'Yes'
WHEN 'ingredient' THEN 'Yes'
WHEN 'branded pack' THEN 'Yes'
WHEN 'clinical pack' THEN 'Yes'
ELSE 'No'
END
ELSE 'No'
END) Is_Drug_Concept_flag
FROM vocabulary.concept C,
vocabulary.vocabulary V
WHERE C.concept_id = 1545999
AND C.vocabulary_id = V.vocabulary_id
AND sysdate BETWEEN C.valid_start_date AND C.valid_end_date
Output:
Output field list:
Field |
Description |
Drug_Concept_ID |
Concept Identifier entered as input |
Drug_Concept_Name |
Name of the standard drug concept |
Drug_Concept_Code |
Concept code of the standard drug concept in the source vocabulary |
Drug_Concept_Class |
Concept class of standard drug concept |
Drug_Concept_Level |
Level of the concept if defined as part of a hierarchy |
Drug_Concept_Vocab_ID |
Vocabulary the standard drug concept is derived from as vocabulary ID |
Drug_Concept_Vocab_Name |
Name of the vocabulary the standard drug concept is derived from |
Is_Drug_Concept_Flag |
Flag indicating whether the Concept ID belongs to a drug concept
‘Yes’ if drug concept, ‘No’ if not a drug concept |
Sample output record:
Field |
Value |
Drug_Concept_ID |
1545999 |
Drug_Concept_Name |
atorvastatin 20 MG Oral Tablet [Lipitor] |
Drug_Concept_Code |
617318 |
Drug_Concept_Class |
Branded Drug |
Drug_Concept_Level |
1 |
Drug_Concept_Vocab_ID |
8 |
Drug_Concept_Vocab_Name |
RxNorm |
Is_Drug_Concept_Flag |
No |
|