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 |
|
|