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.
Input:
Parameter |
Example |
Mandatory |
Notes |
Source Code List |
'410.0' |
Yes |
Source codes are alphanumeric. |
Source Vocabulary ID |
2 |
Yes |
2 represents ICD9-CM.
The list of vocabulary codes can be found in the VOCABULARY table. |
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 and descendants for the set of example codes in the parameter list. The input parameters are highlighted in blue.
SELECT T.mapping_type,
T.target_concept_id,
T.target_Concept_Name,
T.target_concept_code,
T.target_Concept_Class,
T.target_Concept_Vocab_ID,
T.target_Concept_Vocab_Name,
T.target_Type
FROM (
-- collect direct maps
SELECT SC.source_code,
SC.source_vocabulary_id,
SC.mapping_type,
C.concept_id as target_concept_id,
C.concept_name as target_Concept_Name,
C.concept_code as target_concept_code,
C.concept_class as target_Concept_Class,
C.vocabulary_id as target_Concept_Vocab_ID,
VT.vocabulary_name as target_Concept_Vocab_Name,
'Direct map' as target_Type
FROM vocabulary.source_to_concept_map SC,
vocabulary.concept C,
vocabulary.vocabulary VT
WHERE SC.target_concept_id = C.concept_id
AND SC.target_vocabulary_id = VT.vocabulary_id
AND SC.source_code IN ('410.0')
AND SC.source_vocabulary_id = 2
AND sysdate BETWEEN SC.valid_start_date AND SC.valid_end_date
-- collect descendants
UNION
SELECT SC.source_code,
SC.source_vocabulary_id,
SC.mapping_type,
CD.concept_id as target_concept_id,
CD.concept_name as target_Concept_Name,
CD.concept_code as target_concept_code,
CD.concept_class as target_Concept_Class,
CD.vocabulary_id as target_Concept_Vocab_ID,
VT.vocabulary_name as target_Concept_Vocab_Name,
'Descendant of direct map' as target_Type
FROM vocabulary.source_to_concept_map SC,
vocabulary.concept C,
vocabulary.vocabulary VT,
vocabulary.concept_ancestor CA,
vocabulary.concept CD
WHERE SC.target_concept_id = C.concept_id
AND C.concept_id = CA.ancestor_concept_id
AND CA.descendant_concept_id = CD.concept_id
AND CD.vocabulary_id = VT.vocabulary_id
AND CA.ancestor_concept_id <> CA.descendant_concept_id
AND SC.source_code IN ('410.0')
AND SC.source_vocabulary_id = 2
AND sysdate BETWEEN SC.valid_start_date AND SC.valid_end_date
) T
ORDER BY 6,8
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 |
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 |
Target_Concept_Vocab_Name |
Name of the vocabulary the target concept is part of |
Target_Type |
Type of result, indicates how the target concepts was extracted. Includes:
- Concepts that are direct maps
- Concepts that are descendants of direct maps
|
Sample output record 1:
Field |
Value |
Mapping_Type |
CONDITION |
Target_Concept_ID |
312327
|
Target_Concept_Name |
Acute myocardial infarction
|
Target_Concept_Code |
57054005
|
Target_Concept_Class |
Clinical finding
|
Target_Concept_Vocab_ID |
1 |
Target_Concept_Vocab_Name |
SNOMED-CT |
Target_Type |
Direct map
|
Sample output record 2:
Field |
Value |
Mapping_Type |
CONDITION |
Target_Concept_ID |
434376 |
Target_Concept_Name |
Acute myocardial infarction of anterior wall |
Target_Concept_Code |
54329005 |
Target_Concept_Class |
Clinical finding |
Target_Concept_Vocab_ID |
1 |
Target_Concept_Vocab_Name |
SNOMED-CT |
Target_Type |
Descendant of direct map |
|
|