P03: Find indications for a procedure

This query enables a search of all conditions/clinical findings associated with procedures. The query is defined for SNOMED-CT concepts and is based on the “Has focus (SNOMED-CT)” (Relationship ID=46) and “Associated with (SNOMED-CT)” (Relationship ID=79). The scope and coverage of the results are based completely on the SNOMED-CT relationship, which is generally not as comprehensive as the relationships between drugs and indications provided by FDB or NDF-RT (see queries D02).
The query does not include any filters for individual procedures/conditions and returns all condition-procedure relationships present in the vocabulary. The query can be customized with additional filters for individual procedures and conditions/clinical findings.

Input:
 Parameter  Example  Mandatory  Notes
 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 run extract all indications associated with procedures.
SELECT CASE CR.relationship_ID
         WHEN 46 THEN D.concept_id
         ELSE A.concept_id
       END                        Condition_ID,
       CASE CR.relationship_ID
         WHEN 46 THEN D.concept_name
         ELSE A.concept_name
       END                        Condition_Name,
       CASE CR.relationship_ID
         WHEN 46 THEN D.concept_code
         ELSE A.concept_code
       END                        Condition_Code,
       CASE CR.relationship_ID
         WHEN 46 THEN D.concept_class
         ELSE A.concept_class
       END                        Condition_class,
       CASE CR.relationship_ID
         WHEN 46 THEN D.vocabulary_id
         ELSE A.vocabulary_id
       END                        Condition_Vocab_ID,
       CASE CR.relationship_ID
         WHEN 46 THEN VS.vocabulary_name
         ELSE VA.vocabulary_name
       END                        Condition_Vocab_Name,
       RT.relationship_name       Relationship_description,
       CASE CR.relationship_ID
         WHEN 46 THEN A.concept_id
         ELSE D.concept_id
       END                        Procedure_ID,
       CASE CR.relationship_ID
         WHEN 46 THEN A.concept_name
         ELSE D.concept_name
       END                        Procedure_Name,
       CASE CR.relationship_ID
         WHEN 46 THEN A.concept_code
         ELSE D.concept_code
       END                        Procedure_Code,
       CASE CR.relationship_ID
         WHEN 46 THEN A.concept_class
         ELSE D.concept_class
       END                        Procedure_Class,
       CASE CR.relationship_ID
         WHEN 46 THEN A.vocabulary_id
         ELSE D.vocabulary_id
       END                        Procedure_vocab_ID,
       CASE CR.relationship_ID
         WHEN 46 THEN VA.vocabulary_name
         ELSE VS.vocabulary_name
       END                        Procedure_Vocab_Name
FROM   vocabulary.concept_relationship   CR,
       vocabulary.concept                A,
       vocabulary.concept                D,
       vocabulary.vocabulary             VA,
       vocabulary.vocabulary             VS,
       vocabulary.relationship           RT
WHERE  CR.relationship_ID IN (46, 79)
AND    CR.concept_id_1 = A.concept_id
AND    A.vocabulary_id = VA.vocabulary_id
AND    CR.concept_id_2 = D.concept_id
AND    D.vocabulary_id = VS.vocabulary_id
AND    CR.relationship_ID = RT.relationship_ID
AND    LOWER(A.concept_class) IN ('clinical finding', 'procedure')
AND    LOWER(D.concept_class) IN ('clinical finding', 'procedure')
AND    LOWER(A.concept_class) <> LOWER(D.concept_class)
AND    sysdate BETWEEN A.valid_start_date AND A.valid_end_date
AND    sysdate BETWEEN D.valid_start_date AND D.valid_end_date
Output:
Output field list:
 Field  Description
 Condition_ID  Concept ID of SNOMED-CT disease/condition
 Condition_Name  Concept name of SNOMED-CT disease/conditiont
 Condition_Code  Concept code of SNOMED-CT disease/condition
 Condition_Class  Concept class of SNOMED-CT disease/condition
 Condition_vocab_ID  Vocabulary ID of the vocabulary from which the disease/condition concept is derived from
 Condition_vocab_name  Name of the vocabulary from which the disease/condition concept is derived from
 Relationship_description  Description of the relationship between the condition and procedure
 Procedure_ID  Concept ID of SNOMED-CT procedure concept
 Procedure_Name  Name of SNOMED-CT procedure
 Procedure_Code  Concept Code of SNOMED-CT procedure concept
 Procedure_Vocab_ID  Source vocabulary the procedure concept is derived from, expressed as vocabulary ID
 Procedure_Vocab_Name  Name of the vocabulary the procedure concept is derived from

Sample output record:
 Field  Value
 Condition_ID  4012261
 Condition_Name  Immunodeficiency secondary to radiation therapy
 Condition_Code  103080003
 Condition_Class  Clinical finding
 Condition_vocab_ID  1
 Condition_vocab_name  SNOMED-CT
 Relationship_description  Associated with (SNOMED-CT)
 Procedure_ID  4029715
 Procedure_Name  RT – Radiotherapy
 Procedure_Code  108290001
 Procedure_Vocab_ID  1
 Procedure_Vocab_Name  SNOMED-CT
Comments