This query enables search of vocabulary entities by keyword. The query does a search of standard concepts names in the CONDITION domain (SNOMED-CT clinical findings and MedDRA concepts) and their synonyms to return all related concepts.
It does not require prior knowledge of where in the logic of the vocabularies the entity is situated.
Sample query run:
|| 'myocardial infarction'
||Keyword should be placed in a single quote
| As of date
||Valid record as of specific date. Current date – sysdate is a default
The following is a sample run of the query to run a search of the Condition domain for keyword ‘myocardial infarction’. The input parameters are highlighted in blue
SELECT C.concept_id Entity_Concept_Id,
FROM vocabulary.concept C
INNER JOIN vocabulary.vocabulary V ON C.vocabulary_id = V.vocabulary_id
LEFT OUTER JOIN vocabulary.concept_synonym S ON C.concept_id = S.concept_id
C.vocabulary_id IN (2, 15)
OR LOWER(C.concept_class) = 'clinical finding'
AND C.concept_class IS NOT NULL
AND C.concept_level <> 0
INSTR(LOWER(C.concept_name), LOWER('myocardial infarction')) > 0
OR INSTR(LOWER(S.concept_synonym_name), LOWER('myocardial infarction')) > 0
WHERE sysdate BETWEEN valid_start_date AND valid_end_date
ORDER BY 6, 2
Output field list:
|| Concept ID of entity with string match on name or synonym concept
|| Concept name of entity with string match on name or synonym concept
|| Concept code of entity with string match on name or synonym concept
|| Concept type
|| Concept class of entity with string match on name or synonym concept
|| ID of vocabulary associated with the concept
|| Name of the vocabulary associated with the concept
Sample output record:
|| Acute myocardial infarction
|| Preferred Term
This is a comprehensive query to find relevant terms in the vocabulary. To constrain, additional clauses can be added to the query. However, it is recommended to do a filtering after the result set is produced to avoid syntactical mistakes.
The query only returns concepts that are part of the Standard Vocabulary, ie. they have concept level that is not 0. If all concepts are needed, including the non-standard ones, the clause in the query restricting the concept level and concept class can be commented out.