inst/QueryLibrary/Aggregate/condition/C02.md

In sib-swiss/dsQueryLibraryServer: OMOP query Library

C02: Find a condition by keyword

Description

This query enables search of vocabulary entities by keyword. The query does a search of standard concepts names in the CONDITION domain (SNOMED-CT clinical findings and MedDRA concepts) and their synonyms to return all related concepts.

It does not require prior knowledge of where in the logic of the vocabularies the entity is situated.

This is a comprehensive query to find relevant terms in the vocabulary. 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.

Query

The following is a sample run of the query to run a search of the Condition domain for keyword 'myocardial infarction'.

SELECT
  t.entity_concept_id,
  t.entity_name,
  t.entity_code,
  t.entity_concept_class,
  t.entity_vocabulary_id
FROM (
       SELECT
         c.concept_id       AS entity_concept_id,
         c.concept_name     AS entity_name,
         c.concept_code     AS entity_code,
         c.concept_class_id AS entity_concept_class,
         c.vocabulary_id    AS entity_vocabulary_id
       FROM @vocab.concept AS c
         LEFT JOIN @vocab.concept_synonym AS s ON c.concept_id = s.concept_id
       WHERE
          c.invalid_reason IS NULL AND
         (c.vocabulary_id IN ('SNOMED', 'MedDRA') OR lower(c.concept_class_id) = 'clinical finding') AND
         c.concept_class_id IS NOT NULL AND
         (lower(c.concept_name) LIKE CONCAT('%',lower($1::text),'%') OR
          lower(s.concept_synonym_name) LIKE CONCAT('%',lower($1::text),'%'))
     ) t
ORDER BY t.entity_vocabulary_id, t.entity_name
;

Input

| Parameter | Example | Mandatory | Notes | | --- | --- | --- | --- | | Keyword | 'myocardial infarction' | Yes | Keywords are not case sensitive |

Output

| 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 | Concept type | | Entity_Concept_Class | Concept class of entity with string match on name or synonym concept | | Entity_Vocabulary_ID | ID of vocabulary associated with the concept |

Example output record

| Field | Value | | --- | --- | | Entity_Concept_ID | 35205180 | | Entity_Name | Acute myocardial infarction | | Entity_Code | 10000891 | | Entity_Type | Concept | | Entity_Concept_Class | Preferred Term | | Entity_Vocabulary_ID | MedDRA |

Documentation

https://github.com/OHDSI/CommonDataModel/wiki/concept_synonym

sib-swiss/dsQueryLibraryServer documentation built on Feb. 13, 2025, 8:07 p.m.