C08: Find a disease causing agent by keyword

This query enables a search of various agents that can cause disease by keyword as input. Apart from pathogens (see query C07), these agents can be SNOMED-CT concepts of the following classes:
  • Pharmaceutical / biologic product
  • Physical object
  • Special concept
  • Event
  • Physical force
  • Substance
The resulting concepts could be used in query C09 to identify diseases caused by the agent.

 Parameter  Example  Mandatory  Notes
 Keyword for pathogen  'Radiation'  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 pathogens specified using a keyword as input. The sample parameter substitutions are highlighted in blue.
SELECT  C.concept_id       Agent_Concept_ID,
        C.concept_name     Agent_Concept_Name,
        C.concept_code     Agent_concept_code,
        C.concept_class    Agent_concept_class,
        C.concept_level    Agent_Concept_Level,
        C.vocabulary_id    Agent_Concept_Vocab_ID,
        V.vocabulary_name  Agent_Concept_Vocab_Name
FROM    vocabulary.concept     C, 
        vocabulary.vocabulary  V
WHERE   LOWER(C.concept_class) in ('pharmaceutical / biologic product','physical object','special concept','event',
                                   'physical force','substance')
AND     INSTR(LOWER(C.concept_name), LOWER('Radiation')) > 0
AND     C.vocabulary_id = V.vocabulary_id
AND     sysdate BETWEEN C.valid_start_date AND C.valid_end_date
Output field list:
 Field  Description
 Agent_Concept_ID  Concept ID of SNOMED-CT agent concept
 Agent_Concept_Name  Name of SNOMED-CT concept
 Agent_Concept_Code  Concept Code of SNOMED-CT concept
 Agent_Concept_Class  Concept class of SNOMED-CT concept
 Agent_Concept_Level  Concept level of SNOMED-CT concept
 Agent_Vocab_ID  Vocabulary ID of the vocabulary from which the agent concept is derived from (1 for SNOMED-CT)
 Agent_Vocab_Name  Name of the vocabulary from which the agent concept is derived from (SNOMED-CT)

Sample output record:
 Field  Value
 Agent_Concept_ID  4220084
 Agent_Concept_Name  Radiation
 Agent_Concept_Code  82107009
 Agent_Concept_Class  Physical force
 Agent_Concept_Level  0
 Agent_Vocab_ID  1
 Agent_Vocab_Name  SNOMED-CT