inst/QueryLibrary/Aggregate/general/G06.md

In sib-swiss/dsQueryLibraryServer: OMOP query Library

G06: Find concepts and their descendants that are covered by a given source code

Description

This query returns all concepts that are direct maps and the descendants of these directly mapped concepts. This is useful if the target standard vocabulary is organized in a tall hierarchy, while the source vocabulary organization is flat.

Additional constraints can be added at the end of the query if only a specific target domain or target vocabulary is desired. For example, if only SNOMED-CT as the standard vocabulary for conditions needs be returned, the target vocabulary can be set to 1.

In the query only FDB indications and contraindications are returned, but not NDF-RT indications or contraindications. That is because no direct mapping between ICD-9-CM and NDF-RT exists. In order to query for drug indications please see queries D12 through D18.

Query

WITH parms as (select cid as cid  from unnest(regexp_split_to_array( nullif($1::text, '')::text, '\s*,\s*')) as cid), dm AS ( 
    SELECT
      c1.concept_code     AS source_code,
      c1.vocabulary_id    AS source_vocabulary_id,
      c1.domain_id        AS source_domain_id,
      c2.concept_id       AS target_concept_id,
      c2.concept_name     AS target_concept_name,
      c2.concept_code     AS target_concept_code,
      c2.concept_class_id AS target_concept_class,
      c2.vocabulary_id    AS target_concept_vocab_id
    FROM @vocab.concept_relationship AS cr
      JOIN @vocab.concept AS c1 ON cr.concept_id_1 = c1.concept_id
      JOIN @vocab.concept AS c2 ON cr.concept_id_2 = c2.concept_id
    WHERE
      cr.relationship_id = 'Maps to' AND
      c1.concept_code IN (select cid from parms) AND
      c1.vocabulary_id = $2 AND
      cr.invalid_reason IS NULL
)
SELECT
  dm.source_code,
  dm.source_vocabulary_id,
  dm.source_domain_id,
  dc.concept_id       AS target_concept_id,
  dc.concept_name     AS target_concept_name,
  dc.concept_code     AS target_concept_code,
  dc.concept_class_id AS target_concept_class,
  dc.vocabulary_id    AS target_concept_vocab_id
FROM @vocab.concept_ancestor AS ca
  JOIN dm ON ca.ancestor_concept_id = dm.target_concept_id
  JOIN @vocab.concept AS dc ON ca.descendant_concept_id = dc.concept_id
WHERE dc.standard_concept = 'S'
;

Input

| Parameter | Example | Mandatory | Notes | | --- | --- | --- | --- | | Source Code List | 99998006, 95964009, 9596311000001107| Yes | Source codes are alphanumeric. | | Source Vocabulary ID | SNOMED | Yes | |

Output

| Field | Description | | --- | --- | | Source_Code | Concept code of source concept | | Source_Vocab_ID | ID of the source vocabulary | | Source_Domain_ID | Domain of the source concept | | Target_Concept_ID | Concept ID of mapped concept | | Target_Concept_Name | Concept name of mapped concept | | Target_Concept_Code | Concept Code of mapped concept | | Target_Concept_Class | Concept class of mapped concept | | Target_Concept_Vocab_ID | ID of the target vocabulary |

Example output record

| Field | Value | | --- | --- | | Source_Code | 410.0 | | Source_Vocab_ID | ICD9CM | | Source_Domain_ID | CONDITION | | Target_Concept_ID | 438438 | | Target_Concept_Name | Acute myocardial infarction of anterolateral wall | | Target_Concept_Code | 70211005 | | Target_Concept_Class | Clinical finding | | Target_Concept_Vocab_ID | SNOMED |

Documentation

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

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