This query enables search of vocabulary entities by keyword irrespective where in the vocabulary the entity is situated. The keyword search is run against the following entities in the vocabulary:
- Standard concept names in the vocabulary
- Synonyms for standard concepts
- Descriptions of mapped concepts
A list of the resulting entities is then presented as output with entity details including identifiers, domain and names.
This is a comprehensive query to screen the entire vocabulary for a term of interest, if it is not clear where it could be found. The query returns only records that have a precise string match. There is no white space manipulation or removal of stop words or signs.
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. those having concept level that is not 0. If all concepts are needed, including the non-standard ones, the clause can be modified accordingly.
Input:
| Parameter |
Example |
Mandatory |
Notes |
| Keyword |
'myocardial infarction' |
Yes |
Keyword string should be enclosed with a single quote |
| As of date |
Sysdate |
No |
Valid record as of specific date. Current date – sysdate is a default |
Sample query run:
The following is a sample run of the query to run a search for keyword ‘myocardial infarction’. The input parameters are highlighted in blue.
SELECT DISTINCT
T.Entity_Concept_Id,
T.Entity_Name,
T.Entity_Code,
T.Entity_Type,
T.Entity_concept_class,
T.Entity_vocabulary_id,
T.Entity_vocabulary_name,
(
CASE T.Entity_vocabulary_id
WHEN 0 THEN 'No matching concept'
WHEN 1 THEN (
CASE UPPER(T.Entity_concept_class)
WHEN 'CLINICAL FINDING' THEN 'CONDITION'
WHEN 'PROCEDURE' THEN 'PROCEDURE'
WHEN 'PATIENT STATUS' THEN 'PATIENT STATUS'
ELSE 'OTHER'
END
)
WHEN 2 THEN 'CONDITION'
WHEN 3 THEN 'PROCEDURE'
WHEN 4 THEN 'PROCEDURE'
WHEN 5 THEN 'PROCEDURE'
WHEN 6 THEN 'OBSERVATION'
WHEN 7 THEN 'DRUG'
WHEN 8 THEN 'DRUG'
WHEN 9 THEN 'DRUG'
WHEN 10 THEN 'DRUG'
WHEN 11 THEN 'OBSERVATION UNIT'
WHEN 12 THEN 'DEMOGRAPHIC'
WHEN 13 THEN 'DEMOGRAPHIC'
WHEN 14 THEN 'VISIT'
WHEN 15 THEN 'CONDITION'
WHEN 16 THEN 'DRUG'
WHEN 17 THEN (
CASE UPPER(T.Entity_mapping_type)
WHEN 'CONDITION' THEN 'CONDITION'
WHEN 'PROCEDURE' THEN 'PROCEDURE'
ELSE 'OTHER'
END
)
WHEN 18 THEN (
CASE UPPER(T.Entity_mapping_type)
WHEN 'CONDITION' THEN 'CONDITION'
WHEN 'PROCEDURE' THEN 'PROCEDURE'
ELSE 'OTHER'
END
)
WHEN 19 THEN 'DRUG'
WHEN 20 THEN 'DRUG'
WHEN 21 THEN 'DRUG'
WHEN 22 THEN 'DRUG'
WHEN 24 THEN 'VISIT'
WHEN 25 THEN 'DEMOGRAPHIC'
WHEN 26 THEN 'DEMOGRAPHIC'
WHEN 27 THEN 'DEMOGRAPHIC'
WHEN 28 THEN 'DRUG'
WHEN 31 THEN 'COHORT'
WHEN 32 THEN 'DRUG'
WHEN 33 THEN 'COHORT'
WHEN 34 THEN 'CONDITION'
WHEN 35 THEN 'PROCEDURE'
WHEN 36 THEN 'DRUG'
WHEN 37 THEN 'CONDITION'
WHEN 38 THEN 'PROCEDURE'
WHEN 39 THEN 'OBSERVATION'
WHEN 40 THEN 'COST'
WHEN 41 THEN 'COST'
WHEN 42 THEN 'COST'
WHEN 43 THEN 'COST'
WHEN 44 THEN 'DEMOGRAPHIC'
WHEN 45 THEN 'DEATH'
WHEN 46 THEN 'DRUG'
WHEN 47 THEN 'PROVIDER'
WHEN 48 THEN 'PROVIDER'
WHEN 49 THEN 'OBSERVATION'
WHEN 50 THEN 'DRUG'
WHEN 51 THEN 'DRUG'
WHEN 53 THEN 'DRUG'
END
) Entity_Domain
FROM (
SELECT C.concept_id Entity_Concept_Id,
C.concept_name Entity_Name,
C.concept_code Entity_Code,
'Concept' Entity_Type,
C.concept_class Entity_concept_class,
C.vocabulary_id Entity_vocabulary_id,
V.vocabulary_name Entity_vocabulary_name,
C.concept_level Entity_Concept_level,
NULL Entity_Mapping_Type,
valid_start_date,
valid_end_date
FROM vocabulary.concept C,
vocabulary.vocabulary V
WHERE C.vocabulary_id = V.vocabulary_id
AND INSTR(LOWER(C.concept_name), LOWER('myocardial infarction')) > 0
AND (C.concept_class IS NULL
OR C.concept_level <> 0)
AND sysdate BETWEEN valid_start_date AND valid_end_date
UNION
SELECT DISTINCT
CAST(NULL AS Decimal) Entity_Concept_Id,
SC.source_code_description Entity_Name,
SC.source_code Entity_Code,
'Mapped Code' Entity_Type,
VC.concept_class Entity_concept_class,
SC.source_vocabulary_id Entity_vocabulary_id,
V.vocabulary_name Entity_vocabulary_name,
VC.concept_level Entity_Concept_level,
SC.mapping_type Entity_Mapping_Type,
SC.valid_start_date,
SC.valid_end_date
FROM vocabulary.source_to_concept_map SC,
vocabulary.vocabulary V,
vocabulary.concept VC
WHERE SC.SOURCE_vocabulary_id = V.vocabulary_id
AND VC.concept_id = SC.target_concept_id
AND INSTR(LOWER(SC.source_code_description), LOWER('myocardial infarction')) > 0
AND (VC.concept_class IS NULL
OR VC.concept_level <> 0)
AND sysdate BETWEEN SC.valid_start_date AND SC.valid_end_date
AND SC.PRIMARY_MAP = 'Y'
UNION ALL
SELECT C.concept_id Entity_Concept_Id,
S.concept_synonym_name Entity_Name,
C.concept_code Entity_Code,
'Concept Synonym' Entity_Type,
C.concept_class Entity_concept_class,
C.vocabulary_id Entity_vocabulary_id,
V.vocabulary_name Entity_vocabulary_name,
C.concept_level Entity_Concept_level,
NULL Entity_Mapping_Type,
valid_start_date,
valid_end_date
FROM vocabulary.concept C,
vocabulary.concept_synonym S,
vocabulary.vocabulary V
WHERE S.concept_id = C.concept_id
AND C.vocabulary_id = V.vocabulary_id
AND INSTR(LOWER(S.concept_synonym_name), LOWER('myocardial infarction')) > 0
AND (C.concept_class IS NULL
OR C.concept_level <> 0)
AND sysdate BETWEEN valid_start_date AND valid_end_date
) T
ORDER BY 8, 4, 6, 5, 2
Output:
Output field list:
| Field |
Description |
| Entity_Concept_ID |
Concept ID of entity with keyword match. Applicable to entities that are concepts and concept synonyms |
| Entity_Name |
Name of entity with keyword match |
| Entity_Code |
Appropriate code for the matching entity. They are one of the following:
- Concept code for matching concepts and synonyms
- Mapped codes for matching mapped source codes
|
| Entity_Type |
Type of entity with keyword match, includes one of the following:
- Concept
- Concept Synonym
- Mapped Code
|
| Entity_Concept_Class |
Concept class of entity with keyword match. Applicable to entities that are concepts and concept synonyms |
| Entity_Vocabulary_ID |
Vocabulary the entity with string match is derived from as vocabulary code |
| Entity_Vocabulary_Name |
Name of the Vocabulary the entity with string match is derived from |
| Entity_Domain |
Vocabulary domain that includes the entity. The domains include:
DRUG, CONDITION, PROCEDURE, OBSERVATION, OBSERVATION, VISIT, DEMOGRAPHIC, COST, DEATH, PROVIDER, COHORT, OBSERVATION UNIT
|
Sample output record 1:
| Field |
Value |
| Entity_Concept_ID |
312327
|
| Entity_Name |
Acute Myocardial Infarction
|
| Entity_Code |
57054005 |
| Entity_Type |
Concept |
| Entity_Concept_Class |
Clinical finding |
| Entity_Vocabulary_ID |
1 |
| Entity_Vocabulary_Name |
SNOMED-CT |
| Entity_Domain |
CONDITION |
Sample output record 2:
/div>
| Field |
Value |
| Entity_Concept_ID |
2617496 |
| Entity_Name |
ACUTE MYOCARDIAL INFARCTION: PATIENT DOCUMENTED TO HAVE RECEIVED ASPIRIN AT ARRIVAL |
| Entity_Code |
G8006 |
| Entity_Type |
Concept |
| Entity_Concept_Class |
HCPCS |
| Entity_Vocabulary_ID |
5 |
| Entity_Vocabulary_Name |
HCPCS |
| Entity_Domain |
PROCEDURE |
Sample output record 3:
| Field |
Value |
| Entity_Concept_ID |
|
| Entity_Name |
Acute myocardial infarction of anterolateral wall |
| Entity_Code |
410.0 |
| Entity_Type |
Mapped Code |
| Entity_Concept_Class |
|
| Entity_Vocabulary_ID |
2 |
| Entity_Vocabulary_Name |
ICD-9-CM |
| Entity_Domain |
CONDITION |
|
