General Queries‎ > ‎

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

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.

Input:
 Parameter  Example  Mandatory  Notes
 Source Code List  '070.0'  Yes  Source codes are alphanumeric
 Source Vocabulary ID  2  Yes The source vocabulary ID is mandatory, because the source code is not unique across different vocabularies.

The list of vocabulary codes is listed in the VOCABULARY table. Vocabulary ID of 2 represents ICD9-CM
 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 extract mapped concepts for the set of example codes in the parameter list. The input parameters are highlighted in blue. 
SELECT DISTINCT
        SC.mapping_type,
        C.concept_id         as Concept_Id,
        C.concept_name       as Concept_Name,
        C.concept_code       as Concept_Code,
        C.concept_class      as Concept_Class,
        C.vocabulary_id      as Concept_Vocabulary_ID,
        VT.vocabulary_name   as Concept_Vocabulary_Name,
(
CASE SC.target_vocabulary_ID
  WHEN 0  THEN 'No matching concept'
  WHEN 1  THEN (
    CASE UPPER(C.concept_class)
    WHEN 'CLINICAL FINDING' THEN 'CONDITION'
    WHEN 'PROCEDURE' THEN 'PROCEDURE'
    WHEN 'PATIENT STATUS' THEN 'PATIENT STATUS'
    ELSE 'OTHER'
    END
  )
 WHEN 2  THEN 'CONDITION' 
 WHEN 3  THEN 'PROCEDURE'
 WHEN 4  THEN 'PROCEDURE'
 WHEN 5  THEN 'PROCEDURE'
 WHEN 6  THEN 'OBSERVATION'
 WHEN 7  THEN 'DRUG'
 WHEN 8  THEN 'DRUG'
 WHEN 9  THEN 'DRUG'
 WHEN 10 THEN 'DRUG'
 WHEN 11 THEN 'OBSERVATION UNIT'
 WHEN 12 THEN 'DEMOGRAPHIC'
 WHEN 13 THEN 'DEMOGRAPHIC'
 WHEN 14 THEN 'VISIT'
 WHEN 15 THEN 'CONDITION'
 WHEN 16 THEN 'DRUG'
 WHEN 17 THEN (
  CASE UPPER(SC.mapping_type)
    WHEN 'CONDITION' THEN 'CONDITION'
    WHEN 'PROCEDURE' THEN 'PROCEDURE'
    ELSE 'OTHER'
    END
  )
 WHEN 18 THEN (
  CASE UPPER(SC.mapping_type)
    WHEN 'CONDITION' THEN 'CONDITION'
    WHEN 'PROCEDURE' THEN 'PROCEDURE'
    ELSE 'OTHER'
    END
  )
 WHEN 19 THEN 'DRUG'
 WHEN 20 THEN 'DRUG'
 WHEN 21 THEN 'DRUG'
 WHEN 22 THEN 'DRUG'
 WHEN 24 THEN 'VISIT'
 WHEN 25 THEN 'DEMOGRAPHIC'
 WHEN 26 THEN 'DEMOGRAPHIC'
 WHEN 27 THEN 'DEMOGRAPHIC'
 WHEN 28 THEN 'DRUG'
 WHEN 31 THEN 'COHORT'
 WHEN 32 THEN 'DRUG'
 WHEN 33 THEN 'COHORT'
 WHEN 34 THEN 'CONDITION'
 WHEN 35 THEN 'PROCEDURE'
 WHEN 36 THEN 'DRUG'
 WHEN 37 THEN 'CONDITION'
 WHEN 38 THEN 'PROCEDURE'
 WHEN 39 THEN 'OBSERVATION'
 WHEN 40 THEN 'COST'
 WHEN 41 THEN 'COST'
 WHEN 42 THEN 'COST'
 WHEN 43 THEN 'COST'
 WHEN 44 THEN 'DEMOGRAPHIC'
 WHEN 45 THEN 'DEATH'
 WHEN 46 THEN 'DRUG'
 WHEN 47 THEN 'PROVIDER'
 WHEN 48 THEN 'PROVIDER'
 WHEN 49 THEN 'OBSERVATION'
 WHEN 50 THEN 'DRUG'
 WHEN 51 THEN 'DRUG'
 WHEN 53 THEN 'DRUG'
 END
 )  target_Concept_Domain
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.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 C.valid_start_date AND C.valid_end_date
ORDER BY 1, 5

Output:
Output field list
 Field  Description
 Mapping_Type  Type of mapping from source code to target 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_Vocab_Name  Name of the vocabulary the target concept is part of
 Target_Concept_Domain  Vocabulary domain that includes the entity. The domains include:
DRUG, CONDITION, PROCEDURE, OBSERVATION, OBSERVATION UNIT, VISIT, DEMOGRAPHIC, DEATH, COST, PROVIDER

Sample output record:
 Field  Value
 Mapping_Type  CONDITION-MEDDRA
 Target_Concept_Id  35909589
 Target_Concept_Name  Hepatitis viral
 Target_Concept_Code  10019799
 Target_Concept_Class  Preferred Term
 Target_Concept_Vocab_ID  15
 Target_Concept_Vocab_Name  MedDRA
 Target_Concept_Domain  CONDITION
Comments