inst/QueryLibrary/Aggregate/general/G05.md

In sib-swiss/dsQueryLibraryServer: OMOP query Library

G05: Translate a code from a source to a standard vocabulary.

Description

This query enables search of all Standard Vocabulary concepts that are mapped to a code from a specified source vocabulary. It will return all possible concepts that are mapped to it, as well as the target vocabulary. The source code could be obtained using queries G02 or G03. Note that to unambiguously identify a source code, the vocabulary id has to be provided, as source codes are not unique identifiers across different vocabularies.

Query

WITH parms as (select cid as cid  from unnest(regexp_split_to_array( nullif($1::text, '')::text, '\s*,\s*')) as cid) 
SELECT DISTINCT
  c1.domain_id        AS source_domain,
  c2.concept_id       AS concept_id,
  c2.concept_name     AS concept_name,
  c2.concept_code     AS concept_code,
  c2.concept_class_id AS concept_class,
  c2.vocabulary_id    AS concept_vocabulary_id,
  c2.domain_id        AS target_concept_domain
FROM @vocab.concept_relationship AS cr
  JOIN @vocab.concept AS c1 ON c1.concept_id = cr.concept_id_1
  JOIN @vocab.concept AS c2 ON c2.concept_id = cr.concept_id_2
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
;

Input

| Parameter | Example | Mandatory | Notes | | --- | --- | --- | --- | | Source Code List | 99998006, 95964009, 9596311000001107| Yes | Source codes are alphanumeric | | Source Vocabulary ID | SNOMED| Yes | The source vocabulary ID is mandatory, because the source code is not unique across different vocabularies. |

Output

| Field | Description | | --- | --- | | Source_Domain | Domain of source concept | | Target_Concept_Id | Concept ID of mapped concept | | Target_Concept_Name | Name of mapped concept | | Target_Concept_Code | Concept code of mapped concept | | Target_Concept_Class | Class of the mapped concept | | Target_Concept_Vocab_ID | Vocabulary ID of the target vocabulary | | Target_Concept_Domain | Vocabulary domain that includes the entity. The domains include: DRUG, CONDITION, PROCEDURE, OBSERVATION, OBSERVATION UNIT, VISIT, DEMOGRAPHIC, DEATH, COST, PROVIDER |

Example output record

| Field | Value | | --- | --- | | Source_Domain | Condition | | Target_Concept_Id | 194856 | | Target_Concept_Name | Hepatic coma due to viral hepatitis A | | Target_Concept_Code | 16060001 | | Target_Concept_Class | Clinical Finding | | Target_Concept_Vocab_ID | SNOMED | | Target_Concept_Domain | Concept |

Documentation

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

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