General Queries‎ > ‎

G06: Find concepts and their descendants that are covered by a given source code

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
Comments