C05: Translate a source code to condition concepts

This query enables to search all Standard SNOMED-CT concepts that are mapped to a condition (disease) source code. It can be used to translate e.g. ICD-9-CM, ICD-10-CM or Read codes to SNOMED-CT.
Source codes are not unique across different source vocabularies, therefore the source vocabulary ID must also be provided.
The following source vocabularies have condition/disease codes that map to SNOMED-CT concepts:
  •  ICD-9-CM,    Vocabulary_id=2
  • Read,            Vocabulary_id=17
  • OXMIS,         Vocabulary_id=18
  • ICD-10-CM,   Vocabulary_id=34

Input:
 Parameter  Example  Mandatory  Notes
 Source Code List  '070.0'  Yes Source codes are alphanumeric and need to be entered as a string enclosed by a single quote. If more than one source code needs to be entered an IN clause or a JOIN can be used.
 Source Vocabulary ID  2  Yes The source vocabulary is mandatory, because the source ID is not unique across different vocabularies.
 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 SNOMED-CT concepts that a set of mapped codes entered as input map to. The sample parameter substitutions are highlighted in blue:
SELECT DISTINCT
       SC.source_code,
       SC.source_code_description,
       SC.source_vocabulary_id,
       VS.vocabulary_name          source_vocabulary_description,
       SC.mapping_type,
       C.concept_id                target_concept_id,
       C.concept_name              target_Concept_Name,
       C.concept_code              target_Concept_Code,
       C.concept_class             target_Concept_Class,
       C.vocabulary_id             target_Concept_Vocab_ID,
       VT.vocabulary_name          target_Concept_Vocab_Name
FROM   vocabulary.source_to_concept_map         SC,
       vocabulary.concept                       C,
       vocabulary.vocabulary                    VS,
       vocabulary.vocabulary                    VT
WHERE  SC.source_vocabulary_id = VS.vocabulary_id
AND    SC.mapping_type         = 'CONDITION'
AND    SC.target_concept_id    = C.concept_id
AND    SC.target_vocabulary_id = VT.vocabulary_id
AND    SC.source_code IN ('070.0' )
AND    SC.source_vocabulary_id = 2 
AND    sysdate BETWEEN SC.valid_start_date AND SC.valid_end_date
 Output:
Output field list:
 Field  Description
 Source_Code  Source code for the disease entered as input
 Source_Code_Description  Description of the source code entered as input
 Source_Vocabulary_ID  Vocabulary the disease source code is derived from as vocabulary ID
 Source_Vocabulary_Description  Name of the vocabulary the disease source code is derived from
 Mapping_Type  Type of mapping or mapping domain, from source code to target concept. Example Condition, Procedure, Drug etc.
 Target_Concept_ID  Concept ID of the target condition concept mapped to the disease source code
 Target_Concept_Name  Name of the target condition concept mapped to the disease source code
 Target_Concept_Code  Concept code of the target condition concept mapped to the disease source code
 Target_Concept_Class  Concept class of the target condition concept mapped to the disease source code
 Target_Concept_Vocab_ID  Vocabulary the target condition concept is derived from as vocabulary code
 Target_Concept_Vocab_Name  Name of the vocabulary the condition concept is derived from

Sample output record:
 Field  Value
 Source_Code  070.0
 Source_Code_Description  Viral hepatitis
 Source_Vocabulary_ID  2
 Source_Vocabulary_Description  ICD-9-CM
 Mapping_Type  CONDITION
 Target_Concept_ID  4291005
 Target_Concept_Name  VH - Viral hepatitis
 Target_Concept_Code  3738000
 Target_Concept_Class  Clinical finding
 Target_Concept_Vocab_ID  1
 Target_Concept_Vocab_Name  SNOMED-CT
Comments