Drug Queries‎ > ‎

D26: Find the brand name of a drug

This query is designed to accept a drug concept (both clinical and branded) as input and return a the brand name (or branded ingredient) associated with it. The query is useful to check for a brand names associated with a clinical drug. Drug concepts can be obtained using queries G03 or D02.

Input:
 Parameter  Example  Mandatory  Notes
 Drug Concept ID  939355  Yes Can be both clinical and branded drug concepts
 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 all brand names / branded ingredients associated with a set of drug concepts. The input parameters are highlighted in blue.
SELECT A.Concept_Id               drug_concept_id,
        A.Concept_Name            drug_name,
        A.Concept_Code            drug_concept_code,
        A.Concept_Class           drug_concept_class,
        D.Concept_Id              brand_concept_id,
        D.Concept_Name            brand_name,
        D.Concept_Code            brand_concept_code,
        D.Concept_Class           brand_concept_class
FROM   vocabulary.concept_relationship  CR003,
       vocabulary.concept               A,
       vocabulary.concept_relationship  CR007,
       vocabulary.concept_relationship  CR006,
       vocabulary.concept                 D
WHERE  CR003.relationship_ID  = 3
AND    CR003.concept_id_1     = A.concept_id
AND    lower(A.concept_class) = 'clinical drug'
AND    CR007.concept_id_2     = CR003.concept_id_2
AND    CR007.relationship_ID  = 7
AND    CR007.concept_id_1     = CR006.concept_id_1
AND    CR006.relationship_ID  = 6
AND    CR006.concept_id_2     = D.concept_id
AND    lower(D.concept_class) = 'brand name'
AND    A.concept_Id           = 939355
AND    sysdate BETWEEN CR006.VALID_START_DATE AND CR006.VALID_END_DATE
UNION ALL
SELECT A.Concept_Id               drug_concept_id,
       A.Concept_Name             drug_name,
       A.Concept_Code             drug_concept_code,
       A.Concept_Class            drug_concept_class,
       D.Concept_Id               brand_concept_id,
       D.Concept_Name             brand_name,
       D.Concept_Code             brand_concept_code,
       D.Concept_Class            brand_concept_class
FROM   vocabulary.concept               A,
       vocabulary.concept_relationship  CR007,
       vocabulary.concept_relationship  CR006,
       vocabulary.concept               D
WHERE  lower(A.concept_class) = 'branded drug'
AND    CR007.concept_id_2     = A.concept_id
AND    CR007.relationship_ID  = 7
AND    CR007.concept_id_1     = CR006.concept_id_1
AND    CR006.relationship_ID  = 6
AND    CR006.concept_id_2     = D.concept_id
AND    lower(D.concept_class) = 'brand name'
AND    A.concept_Id           = 939355
AND    sysdate BETWEEN CR006.VALID_START_DATE AND CR006.VALID_END_DATE
 Output:
Output field list:
 Field  Description
 Drug_Concept_ID  Concept ID of drug (clinical/generic or branded)
 Drug_Name  Name of drug
 Drug_Concept_Code  Concept code of the drug
 Drug_Concept_Class  Concept class of drug
 Brand_Concept_ID  Concept ID of the brand name (or branded ingredient)
 Brand_name  Name of the brand name
 Brand_Concept_code  Concept code of the brand name
 Brand_Concept_class  Concept Class of the brand name

Sample output record 1:
 Field  Value
 Drug_Concept_ID  19102189
 Drug_Name  Omeprazole 20 MG Enteric Coated Tablet
 Drug_Concept_Code  402014
 Drug_Concept_Class  Clinical Drug
 Brand_Concept_ID  19045785
 Brand_name  Prilosec
 Brand_Concept_code  203345
 Brand_Concept_class  Brand Name

Sample output record 2:
 Field  Value
 Drug_Concept_ID  19033566
 Drug_Name  Lorazepam 0.5 MG Oral Tablet [Ativan]
 Drug_Concept_Code  206821
 Drug_Concept_Class  Branded Drug
 Brand_Concept_ID  19042588
 Brand_name  Ativan
 Brand_Concept_code  202479
 Brand_Concept_class  Brand Name
Comments