workflow.Workflow_ProcedureDependency_T_redundant - V

/*
possible redundant references

we must not remove all of them at the same time, because some of them recognize each other as redundant. +
Therefore marking as `is_redundant` happens one by one for always only one reference by referencing_Procedure_RepoObject_guid

*/
CREATE View workflow.Workflow_ProcedureDependency_T_redundant
As
Select
    T1.Workflow_id
  , T1.referenced_Procedure_RepoObject_guid
  , T1.referencing_Procedure_RepoObject_guid
  , RownrPerReferencing = Row_Number () Over ( Partition By
                                                   T1.Workflow_id
                                                 , T1.referencing_Procedure_RepoObject_guid
                                               Order By
                                                   T1.referenced_Procedure_RepoObject_guid
                                             )
From
    workflow.Workflow_ProcedureDependency_T_active     As T1
    Inner Join
        workflow.Workflow_ProcedureDependency_T_active As T2
            On
            T1.Workflow_id                               = T2.Workflow_id
            And T1.referenced_Procedure_RepoObject_guid  = T2.referenced_Procedure_RepoObject_guid

    Inner Join
        workflow.Workflow_ProcedureDependency_T_active As T3
            On
            T1.Workflow_id                               = T3.Workflow_id
            And T1.referencing_Procedure_RepoObject_guid = T3.referencing_Procedure_RepoObject_guid
            And T2.referencing_Procedure_RepoObject_guid = T3.referenced_Procedure_RepoObject_guid
Where
    T1.is_redundant     = 0
    And T2.is_redundant = 0
    And T3.is_redundant = 0