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

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
Comments