This query is designed to extract all clinical and branded drugs associated with a branded ingredient (or simply a brand name). Since the brand names are not part of the standard drug hierarchy in the OMOP vocabulary, the association between brand name and generic/branded drugs is made using a set of relationships.
The query requires a brand name concept ID as the input. Brand name concept IDs can be obtained by querying the Concept table for a concept class of ‘Brand Name’.
Input:
Parameter |
Example |
Mandatory |
Notes |
Brand name Concept ID |
19011505 |
Yes |
Concept ID for brand name ‘Fosamax’.
Brand name concept IDs are listed in the CONCEPT table with a concept class of ‘Brand name’
|
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 generic/branded drugs associated with a brand name whose concept ID is entered as input. 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 D.concept_id = 19011505
AND sysdate BETWEEN CR006.valid_start_date AND CR006.valid_end_date
AND sysdate BETWEEN CR007.valid_start_date AND CR007.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 D.concept_id = 19011505
AND sysdate BETWEEN CR006.valid_start_date AND CR006.valid_end_date
AND sysdate BETWEEN CR007.valid_start_date AND CR007.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 entered as ingredient |
Brand_name |
Name of the brand |
Brand_Concept_code |
Concept code of the brand name |
Brand_Concept_class |
Concept Class of the brand name
|
Sample output record:
Field |
Value |
Drug_Concept_ID |
40173591 |
Drug_Name |
Alendronic acid 10 MG Oral Tablet [Fosamax] |
Drug_Concept_Code |
904421 |
Drug_Concept_Class |
Branded Drug |
Brand_Concept_ID |
19011505 |
Brand_name |
Fosamax |
Brand_Concept_code |
114265 |
Brand_Concept_class |
Brand Name |
|
|