Example: Self-Remotes - Return Item Level

October 18, 2025 17:44
Updated

Sometimes it is useful to know where in a hierarchy structure an item is placed. This Self-remote returns the hierarchy level of an item.

This example is provided as inspiration only. It must be adapted to your Perfion environment to work. Perfion Support does not assist with such adaptations. Please contact your Perfion Partner or your Perfion consultant if you need assistance.

WITH cteParents as (
        SELECT  
           it.ID,  
           1 as HLEVEL  
        FROM  
           Items it  
        WHERE  
           it.ParentID = 0    
    
        UNION ALL  

        SELECT  
           it.ID,  
           parent.HLEVEL + 1 as HLEVEL  
        FROM  
           cteParents parent  
        INNER JOIN Items it ON parent.ID = it.ParentID  
        )  
          
     SELECT
        ID as KEYVALUE,  
        HLevel  
     FROM cteParents  
     WHERE id IN(#@ID:2558,2561#)

Related articles