This query is designed to extract all drugs that contain a specified ingredient. The query accepts an ingredient concept ID as the input and returns all drugs that have the ingredient. It should be noted that the query returns both generics that have a single ingredient (i.e. the specified ingredient) and those that are combinations which include the specified ingredient.
The query requires the ingredient concept ID as the input. A list of these ingredient concepts can be extracted by querying the concept table for concept class of ‘Ingredient’, e.g. using query D02.
Input:
Parameter |
Example |
Mandatory |
Notes |
Ingredient Concept ID |
966991 |
Yes |
Concept ID for ‘Simethicone’.
Ingredient concepts can be extracted from CONCEPT table as records of concept class of ‘Ingredient’ |
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 drugs that contain an ingredient the concept ID for which is entered as input. The input parameters are highlighted in blue.
SELECT A.concept_id Ingredient_concept_id,
A.concept_Name Ingredient_name,
A.concept_Code Ingredient_concept_code,
A.concept_Class Ingredient_concept_class,
D.concept_id Drug_concept_id,
D.concept_Name Drug_name,
D.concept_Code Drug_concept_code,
D.concept_Class Drug_concept_class
FROM vocabulary.concept_ancestor CA,
vocabulary.concept A,
vocabulary.concept D
WHERE CA.ancestor_concept_id = 966991
AND CA.ancestor_concept_id = A.concept_id
AND CA.descendant_concept_id = D.concept_id
AND sysdate BETWEEN A.valid_start_date AND A.valid_end_date
AND sysdate BETWEEN D.valid_start_date AND D.valid_end_date
Output:
Output field list:
Field |
Description |
Ingredient_Concept_ID |
Concept ID of the ingredient entered as input |
Ingredient_name |
Name of the ingredient |
Ingredient_Concept_code |
Concept code of the ingredient |
Ingredient_Concept_class |
Concept Class of the ingredient |
Generic_Concept_ID |
Concept ID of drug with the ingredient |
Generic_Name |
Name of drug concept with the ingredient |
Generic_Concept_Code |
Concept code of the drug with the ingredient |
Generic_Concept_Class |
Concept class of drug with the ingredient |
Sample output record 1: Single ingredient generic
Field |
Value |
Ingredient_Concept_ID |
966991 |
Ingredient_name |
Simethicone |
Ingredient_Concept_code |
9796 |
Ingredient_Concept_class |
Ingredient |
Generic_Concept_ID |
967306 |
Generic_Name |
Simethicone 10 MG/ML Oral Solution |
Generic_Concept_Code |
251293 |
Generic_Concept_Class |
Clinical Drug |
Sample output record 2: Combination generic
Field |
Value |
Ingredient_Concept_ID |
966991 |
Ingredient_name |
Simethicone |
Ingredient_Concept_code |
9796 |
Ingredient_Concept_class |
Ingredient |
Generic_Concept_ID |
19082312 |
Generic_Name |
Aluminum Hydroxide 200 MG / Magnesium Hydroxide 200 MG / Simethicone 20 MG Chewable Tablet |
Generic_Concept_Code |
317055 |
Generic_Concept_Class |
Clinical Drug |
|