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