C10: Find an anatomical site by keyword

This query enables a search of all anatomical sites using a keyword entered as input. The resulting concepts could be used in query C11 to identify diseases occurring at a certain anatomical site.

Input:
 Parameter  Example  Mandatory  Notes
 Keyword for pathogen  'Epiglottis'  Yes Keyword should be placed in 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 list all anatomical site concept IDs specified using a keyword as input. The sample parameter substitutions are highlighted in blue.
SELECT  C.concept_id       Anatomical_site_ID,
        C.concept_name     Anatomical_site_Name,
        C.concept_code     Anatomical_site_Code,
        C.concept_class    Anatomical_site_Class,
        C.concept_level    Anatomical_site_Level,
        C.vocabulary_id    Anatomical_site_Vocab_ID,
        V.vocabulary_name  Anatomical_site_Vocab_Name
FROM    vocabulary.concept     C, 
        vocabulary.vocabulary  V
WHERE   LOWER(C.concept_class) = 'body structure'
AND     INSTR(LOWER(C.concept_name), LOWER('Epiglottis')) > 0
AND     C.vocabulary_id = V.vocabulary_id
AND     sysdate BETWEEN C.valid_start_date AND C.valid_end_date
Output:
 Field  Description
 Anatomical_site_ID  Concept ID of SNOMED-CT anatomical site concept
 Anatomical_site_Name  Name of SNOMED-CT anatomical site concept entered as input
 Anatomical_site_Code  Concept Code of SNOMED-CT anatomical site concept
 Anatomical_site_Class  Concept class of SNOMED-CT anatomical site
 Anatomical_site_Level  Concept level of SNOMED-CT anatomical site
 Anatomical_site_vocab_ID  Vocabulary ID of the vocabulary from which the anatomical site  concept is derived from
 Anatomical_site_vocab_name  Name of the vocabulary from which the anatomical site concept is derived from

Sample output record:
 Field  Value
 Anatomical_site_ID  4103720
 Anatomical_site_Name  Posterior epiglottis
 Anatomical_site_Code  2894003
 Anatomical_site_Class  Body structure
 Anatomical_site_Level  0
 Anatomical_site_vocab_ID  1
 Anatomical_site_vocab_name  SNOMED-CT
Comments