This query enables the search of LOINC and UCUM descriptions that are used in the observation domain of the vocabulary by keyword.
It does not require prior knowledge of where in the logic of the vocabularies the entity is situated.
Input:
Parameter |
Example |
Mandatory |
Notes |
Keyword |
'LDL' |
Yes |
Keyword search is case insensitive, and spaces and dashes are excluded from the search |
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 of the Observation domain for keyword ‘LDL’. The input parameters are highlighted in blue.
SELECT 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
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.valid_start_date,
C.valid_end_date
FROM vocabulary.concept C,
vocabulary.vocabulary V
WHERE C.vocabulary_id IN (6, 11)
AND C.concept_class IS NOT NULL
AND C.concept_level <> 0
AND C.vocabulary_id = V.vocabulary_id
) T
WHERE INSTR(LOWER(REPLACE(REPLACE(T.Entity_Name, ' ', ''), '-', '')),
LOWER(REPLACE(REPLACE('LDL' , ' ', ''), '-', ''))) > 0
AND sysdate BETWEEN T.valid_start_date AND T.valid_end_date
Output:
Output field list
Field |
Description |
Entity_Concept_ID |
Concept ID of entity with string match on name or synonym concept |
Entity_Name |
Concept name of entity with string match on name or synonym concept |
Entity_Code |
Concept code of entity with string match on name or synonym concept |
Entity_Type |
Type of entity with keyword match (consistent with other keyword search queries elsewhere). Since procedure search is restricted to standard concepts and synonyms, the entity type is always set to ‘Concept’ |
Entity_Concept_Class |
Concept class of entity with string match on name or synonym concept |
Entity_Vocabulary_ID |
Vocabulary the concept with string match is derived from |
Entity_Vocabulary_Name |
Name of the vocabulary the concept code is derived from |
Sample output record:
Field |
Value |
Entity_Concept_ID |
3033200
|
Entity_Name |
Cholesterol in LDL [Mass or Moles/volume] in Serum or Plasma
|
Entity_Code |
35198-1
|
Entity_Type |
Concept
|
Entity_Concept_Class |
LOINC Code
|
Entity_Vocabulary_ID |
6 |
Entity_Vocabulary_Name |
LOINC |
|
|