P02: Find a procedure from a keyword

This query enables search of procedure domain of the vocabulary by keyword. The query does a search of standard concepts names in the PROCEDURE domain (SNOMED-CT procedures, ICD9 procedures, CPT procedures and HCPCS procedures) and their synonyms to return all related concepts.
This is a comprehensive query to find relevant terms in the vocabulary. It does not require prior knowledge of where in the logic of the vocabularies the entity is situated. 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.

Input:
 Parameter  Example  Mandatory  Notes
 Keyword  'artery bypass'  Yes Procedure keyword 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 Procedure domain for keyword ‘artery bypass’. The input parameters are highlighted in blue.
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
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
WHERE  (
              C.vocabulary_id IN (3, 4, 5)
       OR     LOWER(C.concept_class) = 'procedure'
       )
AND    C.concept_class IS NOT NULL
AND    C.concept_level <> 0
AND    (
            INSTR(LOWER(C.concept_name), LOWER('artery bypass')) > 0
       OR   INSTR(LOWER(S.concept_synonym_name), LOWER('artery bypass')) > 0
       )
AND    sysdate BETWEEN C.valid_start_date AND C.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 as vocabulary ID
 Entity_Vocabulary_Name  Name of the vocabulary the concept with string match is derived from as vocabulary code

Sample output record 1: Entity type concept, CPT-4 concept
 Field  Value
 Entity_Concept_ID  2107223
 Entity_Name  Coronary artery bypass, using venous graft(s) and arterial graft(s); two venous grafts (List separately in addition to code for primary procedure)
 Entity_Code  33518
 Entity_Type  Concept
 Entity_Concept_Class  CPT-4
 Entity_Vocabulary_ID  4
 Entity_Vocabulary_Name  CPT-4

Sample output record 2: Entity type concept, SNOMED-CT procedure concept
 Field  Value
 Entity_Concept_ID  4000733
 Entity_Name  Coronary artery bypass graft, anastomosis of artery of thorax to coronary artery
 Entity_Code  119565001
 Entity_Type  Concept
 Entity_Concept_Class  Procedure
 Entity_Vocabulary_ID  1
 Entity_Vocabulary_Name  SNOMED-CT
Comments