This query accepts a mapped drug code instead of a standard drug concept ID as the input. The result set from the returns detailed of indications associated with the drug.
Input:
Parameter |
Example |
Mandatory |
Notes |
Drug Concept ID |
19005968 |
Yes |
Drugs concepts from RxNorm with a concept class of ‘Branded Drug’ |
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 indications associated with a drug concept. The input parameters are highlighted in blue.
SELECT DISTINCT
rn.relationship_name as type_of_indication,
c.concept_id as indication_concept_id,
c.concept_name as indication_concept_name,
c.vocabulary_id as indication_vocabulary_id,
vn.vocabulary_name as indication_vocabulary_name
FROM vocabulary.concept c, vocabulary.vocabulary vn, vocabulary.relationship rn, (
-- collect all indications FROM the drugs, ingredients / pharmaceutical preps AND the type of relationship
SELECT DISTINCT r.relationship_id rid, r.concept_id_2 cid
FROM vocabulary.concept c INNER JOIN (
-- collect onesie clinical AND branded drug if query is ingredient
SELECT onesie.cid concept_id FROM (
SELECT a.descendant_concept_id cid, count(*) cnt FROM vocabulary.concept_ancestor a
INNER JOIN (
SELECT c.concept_id
FROM vocabulary.concept c, vocabulary.concept_ancestor a
WHERE a.ancestor_concept_id=19005968 AND a.descendant_concept_id=c.concept_id AND c.vocabulary_id=8
) cd ON cd.concept_id=a.descendant_concept_id
INNER JOIN vocabulary.concept c on c.concept_id=a.ancestor_concept_id
WHERE c.concept_level=2
GROUP BY a.descendant_concept_id
) onesie WHERE onesie.cnt=1
UNION
-- collect ingredient if query is clinical AND branded drug
SELECT c.concept_id FROM vocabulary.concept c, vocabulary.concept_ancestor a
WHERE a.descendant_concept_id=19005968 AND a.ancestor_concept_id=c.concept_id AND c.vocabulary_id=8
UNION
-- collect pharmaceutical preparation equivalent to which NDFRT has reltionship
SELECT c.concept_id FROM vocabulary.concept c, vocabulary.concept_ancestor a
WHERE a.descendant_concept_id=19005968
AND a.ancestor_concept_id=c.concept_id
AND lower(c.concept_class)='pharmaceutical preparations'
UNION
-- collect itself
SELECT 19005968 FROM dual
) drug ON drug.concept_id=c.concept_id
INNER JOIN vocabulary.concept_relationship r ON c.concept_id=r.concept_id_1
-- allow only indication relationships
WHERE r.relationship_id in (21,23,155,156,126,127,240,241)
) ind
INNER JOIN vocabulary.concept_relationship r ON r.concept_id_1=ind.cid
WHERE r.concept_id_2=c.concept_id AND r.relationship_id in (247, 248)
AND ind.rid=rn.relationship_id AND vn.vocabulary_id=c.vocabulary_id
AND sysdate BETWEEN c.valid_start_date AND c.valid_end_date
Output:
Output field list:
Field |
Description |
Type_of_Indication |
Type of indication, indicating one of the following:
- FDA approved/off-label indication
- Treatment/prevention indication
|
Indication_Concept_ID |
Concept ID of the therapeutic class |
Indication_Concept_Name |
Name of the Indication concept |
Indication_Vocabulary_ID |
Vocabulary the indication is derived from, expressed as vocabulary ID |
Indication_Vocabulary_Name |
Name of the vocabulary the indication is derived from |
Sample output record:
Field |
Value |
Type_of_Indication |
Has FDA-approved drug indication (FDB) |
Indication_Concept_ID |
27674 |
Indication_Concept_Name |
N&V - Nausea and vomiting |
Indication_Vocabulary_ID |
1 |
Indication_Vocabulary_Name |
SNOMED-CT |
|
|