Drug Queries‎ > ‎

D25: Find the generic drugs in a list of drugs

This query is designed to identify generic drug concepts among from a list of standard drug concept IDs. The query identifies branded drugs from the CONCEPT table based on a concept class setting of ‘Clinical Drug’

 Parameter  Example  Mandatory  Notes
 Drug Concept ID list  1396833, 19060643  Yes List of drug concept id’s
 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 generic drug concepts from a list of drug concepts entered as input. The input parameters are highlighted in blue.
SELECT C.concept_id        drug_concept_id,
       C.concept_name      drug_name,
       C.concept_code      drug_concept_code,
       C.concept_class     drug_concept_class,
       C.vocabulary_id     drug_vocabulary_id,
       V.vocabulary_name   drug_vocabulary_name
FROM   vocabulary.concept         C,
       vocabulary.vocabulary      V
WHERE  C.vocabulary_id        = 8
AND    C.concept_id IN (1396833, 19060643)
AND    lower(C.concept_class) = 'clinical drug'
AND    C.vocabulary_id        = V.vocabulary_id
AND    sysdate BETWEEN C.valid_start_date AND C.valid_end_date
Output field list:
 Field  Description
 Drug_Concept_ID  Concept ID of generic drug or pack
 Drug_Name  Name of generic drug or pack
 Drug_Concept_Code  Concept code of generic drug or pack
 Drug_Concept_Class  Concept class of generic drug or pack
 Drug_Vocabulary_ID  Vocabulary the generic drug concept has been derived from, expressed as vocabulary ID
 Drug_Vocabulary_Name  Name of the Vocabulary the generic drug concept has been derived from

Sample output record:
 Field  Value
 Drug_Concept_ID  19060643
 Drug_Name  Budesonide 0.05 MG/ACTUAT Nasal Spray
 Drug_Concept_Code  247042
 Drug_Concept_Class  Clinical Drug
 Drug_Vocabulary_ID  8
 Drug_Vocabulary_Name  RxNorm