General Queries‎ > ‎

G03: Find concepts by keyword anywhere in the vocabulary (concepts, source code descriptions, synonyms)

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
Comments