Hierarchical Queries in SAP HANA Database: Recursive CTEs

Organizational hierarchies are central to SAP SuccessFactors Incentive Management. Sales teams report to managers, managers report to directors, directors report to VPs. Commission results must roll up through the chain. Attainment tracking requires understanding who reports to whom. Recursive CTEs solve this problem elegantly — and if you're migrating from Callidus on Oracle, understanding the difference between CONNECT BY and recursive CTEs is essential.

Why Hierarchy Matters in SuccessFactors IM

Typical hierarchy use cases:

• Org charts: List all employees under a manager (direct reports, sub-reports).
• Rollup totals: Sum commission earned by everyone in a department (including their direct reports).
• Manager approval flows: Find the approver for a participant (walk up the chain to the next manager).
• Tenure tracking: Find all employees hired within a specific manager's domain.
• Quota inheritance: Propagate quota settings down the org tree.

In SuccessFactors IM, CSC_PARTICIPANT has a PARENT_ID column that defines the hierarchy. Each participant optionally has a parent (manager). This parent-child relationship forms a tree structure.

Oracle CONNECT BY: The Old Way

Oracle Database has specialized CONNECT BY syntax for hierarchies:

-- Oracle CONNECT BY: find all employees under manager ID 100
SELECT
  LPAD(' ', (LEVEL-1)*2) || NAME AS ORG_TREE,
  ID,
  PARENT_ID,
  LEVEL
FROM   CSC_PARTICIPANT
START WITH ID = 100
CONNECT BY PRIOR ID = PARENT_ID;

Oracle's CONNECT BY is elegant for the common case: start from a root, walk down the tree following parent-child links. But it's Oracle-specific syntax. HANA doesn't support it.

HANA Recursive CTEs: The Standard Way

Recursive CTEs use standard SQL with a WITH clause. They're part of the ANSI SQL standard, so they work on any modern database (PostgreSQL, SQL Server, Snowflake, etc.). HANA's implementation is efficient and feature-rich.

Recursive CTE Structure

A recursive CTE has two parts:

1. Anchor: The starting rows (base case). Usually a WHERE clause that identifies root nodes or a specific starting point.
2. Recursive member: How to walk from current nodes to next nodes. Uses UNION ALL to combine anchor with recursion.

The pattern:

WITH RECURSIVE cte_name (col1, col2, col3, ...) AS (
  -- ANCHOR: Starting rows
  SELECT col1, col2, col3, ... FROM table
  WHERE starting_condition

  UNION ALL

  -- RECURSIVE: Join CTE back to source table
  SELECT t.col1, t.col2, t.col3, ...
  FROM   table t
  JOIN   cte_name c ON c.some_col = t.parent_col
  WHERE  recursion_depth_limit
)
SELECT * FROM cte_name;

Real Example: Manager Hierarchy

Find all participants under manager ID 100 (direct reports and their sub-reports):

WITH RECURSIVE org_tree (ID, NAME, PARENT_ID, DEPTH) AS (
  -- ANCHOR: Start with the manager
  SELECT
    ID,
    NAME,
    PARENT_ID,
    1 AS DEPTH
  FROM   CSC_PARTICIPANT
  WHERE  ID = 100

  UNION ALL

  -- RECURSIVE: Find all direct reports of current nodes
  SELECT
    p.ID,
    p.NAME,
    p.PARENT_ID,
    t.DEPTH + 1
  FROM   CSC_PARTICIPANT p
  JOIN   org_tree t ON t.ID = p.PARENT_ID
  WHERE  t.DEPTH < 20  -- depth guard: prevent infinite loops
)
SELECT
  LPAD(' ', (DEPTH-1)*2) || NAME AS ORG_CHART,
  ID,
  PARENT_ID,
  DEPTH
FROM   org_tree
ORDER BY DEPTH, NAME;

Breaking this down:

• WITH RECURSIVE org_tree (...): Declare a recursive CTE named org_tree with columns ID, NAME, PARENT_ID, DEPTH.
• Anchor: SELECT ... WHERE ID = 100. This is the starting point: the manager with ID 100.
• UNION ALL: Combines anchor with recursion.
• Recursive: SELECT from CSC_PARTICIPANT p, JOIN to org_tree t WHERE t.ID = p.PARENT_ID. This finds all participants whose parent is already in the tree.
• WHERE t.DEPTH < 20: Depth guard. Prevents infinite loops if the hierarchy has cycles. HANA iterates until no new rows are produced or the guard is hit.
• LPAD: For display: indent by DEPTH for a visual org chart.

How It Executes

The query executes iteratively:

Real Example: Rollup Totals

Calculate total commission earned by everyone under a manager (including the manager):

-- Find all participants under a manager, then sum their earnings
WITH RECURSIVE dept_members (ID, NAME, PARENT_ID) AS (
  -- Anchor: manager
  SELECT ID, NAME, PARENT_ID
  FROM   CSC_PARTICIPANT
  WHERE  ID = 100

  UNION ALL

  -- Recursive: all reports
  SELECT p.ID, p.NAME, p.PARENT_ID
  FROM   CSC_PARTICIPANT p
  JOIN   dept_members d ON d.ID = p.PARENT_ID
)
SELECT
  'Manager ID 100 and team' AS DEPARTMENT,
  COUNT(DISTINCT d.ID) AS PARTICIPANT_COUNT,
  SUM(r.RESULT_AMOUNT) AS TOTAL_EARNED,
  ROUND(AVG(r.RESULT_AMOUNT), 2) AS AVG_RESULT
FROM   dept_members d
LEFT JOIN CSC_RESULTS r ON r.PARTICIPANT_ID = d.ID
WHERE  r.PERIOD_ID = '2026-Q1';

This query:

1. Builds the org tree (manager + all reports) using the recursive CTE.
2. Joins that tree to CSC_RESULTS to get results for each person.
3. Aggregates to show total earned, participant count, and average result size for the entire department.

Real Example: Walk Up the Chain

Find the approver for a participant (walk up to the first manager-level role):

-- Find the approver chain for participant 500
WITH RECURSIVE approval_chain (ID, NAME, PARENT_ID, ROLE, LEVEL) AS (
  -- Anchor: Start with the participant
  SELECT ID, NAME, PARENT_ID, ROLE, 0 AS LEVEL
  FROM   CSC_PARTICIPANT
  WHERE  ID = 500

  UNION ALL

  -- Recursive: Walk up to parent
  SELECT
    p.ID,
    p.NAME,
    p.PARENT_ID,
    p.ROLE,
    c.LEVEL + 1
  FROM   CSC_PARTICIPANT p
  JOIN   approval_chain c ON p.ID = c.PARENT_ID
  WHERE  c.LEVEL < 10  -- Stop after 10 levels
    AND p.ROLE NOT IN ('REP', 'IC') -- Stop at first manager
)
SELECT *
FROM   approval_chain
ORDER BY LEVEL;

Key difference from walking down:

• Down (manager to reports): Anchor is the manager. Recursive joins WHERE child.PARENT_ID = current.ID.
• Up (report to manager): Anchor is the participant. Recursive joins WHERE parent.ID = current.PARENT_ID.

Performance Considerations for Deep Hierarchies

Recursive CTEs can be expensive if your hierarchy is very deep (20+ levels) or very wide (thousands of direct reports at each level). Optimize:

1. Add a Depth Guard

Always include a WHERE clause that limits depth:

2. Filter Early on Indexed Columns

If the recursive CTE is for a specific period or plan, filter inside the CTE:

-- Good: Filter by PLAN_ID in the recursive CTE
WITH RECURSIVE dept_members AS (
  SELECT ... FROM CSC_PARTICIPANT
  WHERE ID = 100 AND PLAN_ID = 'COMP_PLAN_A'
  ...
)

3. Materialize the Tree If Reusing

If you query the same org tree multiple times in one statement, calculate it once and reuse:

-- Build the org tree once, use it in multiple queries
WITH RECURSIVE org_tree AS (...),
     rollup_query AS (
       SELECT ... FROM org_tree LEFT JOIN ...
     ),
     top_performers AS (
       SELECT ... FROM org_tree WHERE ...
     )
SELECT ... FROM rollup_query UNION ALL
SELECT ... FROM top_performers;

4. Test with EXPLAIN PLAN

Check your query's execution plan:

Comparison: Oracle CONNECT BY vs HANA Recursive CTE

Key Takeaways

• Recursive CTEs replace Oracle's CONNECT BY. Use WITH RECURSIVE ... UNION ALL pattern.
• A recursive CTE has two parts: anchor (starting rows) and recursive member (self-join).
• Walking down (manager to reports): anchor is root, recursive joins on child.PARENT_ID = current.ID.
• Walking up (report to manager): anchor is the node, recursive joins on parent.ID = current.PARENT_ID.
• Always add a depth guard (WHERE DEPTH < limit) to prevent infinite loops and runaway costs.
• Combine the recursive CTE with JOIN to facts tables (CSC_RESULTS, CSC_QUOTA) for rollup reporting.
• Recursive CTEs are iterative, not set-based. They work but can be slow on very deep hierarchies. Test with EXPLAIN PLAN.