Drug Queries‎ > ‎

D01: Find drug concept by concept ID

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’.

 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'
         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 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