Lesson 03: Migrating CONNECT BY Hierarchical Queries to Recursive CTEs

Why Hierarchies Matter in ICM

In Callidus Commissions, org hierarchies are critical: departments roll up to divisions, divisions to regions, regions to the company. Commission calculations flow up the hierarchy. Lookups traverse up (find parent manager, find org level). Reporting rolls up by level.

These are classic hierarchical queries. Oracle's CONNECT BY is purpose-built for them. HANA has no direct equivalent, so you rewrite as recursive CTEs. The logic is the same; the syntax is different.

Oracle CONNECT BY: Anatomy

Basic Structure

SELECT column_list
FROM table_name
START WITH anchor_condition
CONNECT BY PRIOR parent_col = child_col
[ORDER BY LEVEL, other_cols];

• START WITH: Anchor clause. Identifies root nodes (usually where parent_id IS NULL).
• CONNECT BY PRIOR: Recursive clause. Says "given the current row, find its children by matching PRIOR id = next parent_id".
• LEVEL pseudo-column: Depth in the tree. Roots have LEVEL=1, their children LEVEL=2, etc.

Example: Organization Hierarchy

SELECT
  ID, PARENT_ID, ORG_NAME,
  LEVEL AS DEPTH
FROM cs_organization
START WITH PARENT_ID IS NULL
CONNECT BY PRIOR ID = PARENT_ID
ORDER BY LEVEL, ORG_NAME;

This query walks from the company root down to the deepest org unit, level by level.

Advanced: SYS_CONNECT_BY_PATH

SYS_CONNECT_BY_PATH builds the full path from root to current node:

SELECT
  ID, PARENT_ID, ORG_NAME,
  LEVEL,
  SYS_CONNECT_BY_PATH(ORG_NAME, '/') AS FULL_PATH
FROM cs_organization
START WITH PARENT_ID IS NULL
CONNECT BY PRIOR ID = PARENT_ID;
-- Output example: 'Company / North Region / Northeast Sales'

Recursive CTE: HANA Equivalent

Basic Structure

WITH RECURSIVE cte_name (column_list) AS (
  -- ANCHOR: Root nodes (equivalent to START WITH)
  SELECT columns
  FROM table_name
  WHERE anchor_condition

  UNION ALL

  -- RECURSIVE: Children (equivalent to CONNECT BY PRIOR)
  SELECT columns
  FROM table_name t
  JOIN cte_name c ON t.parent_col = c.child_col
  WHERE c.depth < max_depth  -- Cycle prevention
)
SELECT * FROM cte_name
ORDER BY depth, name;

Rewrite: Org Hierarchy

WITH RECURSIVE org_hier (
  ID, PARENT_ID, ORG_NAME, DEPTH
) AS (
  -- ANCHOR: Root nodes (START WITH PARENT_ID IS NULL)
  SELECT ID, PARENT_ID, ORG_NAME,
         1 AS DEPTH
  FROM cs_organization
  WHERE PARENT_ID IS NULL

  UNION ALL

  -- RECURSIVE: Children (CONNECT BY PRIOR ID = PARENT_ID)
  SELECT o.ID, o.PARENT_ID, o.ORG_NAME,
         h.DEPTH + 1
  FROM cs_organization o
  JOIN org_hier h ON o.PARENT_ID = h.ID
  WHERE h.DEPTH < 50  -- Cycle prevention (max 50 levels)
)
SELECT ID, PARENT_ID, ORG_NAME, DEPTH
FROM org_hier
ORDER BY DEPTH, ORG_NAME;

With SYS_CONNECT_BY_PATH Equivalent

WITH RECURSIVE org_hier (
  ID, PARENT_ID, ORG_NAME, DEPTH, FULL_PATH
) AS (
  -- ANCHOR: Root nodes
  SELECT ID, PARENT_ID, ORG_NAME,
         1 AS DEPTH,
         TO_NVARCHAR(ORG_NAME) AS FULL_PATH
  FROM cs_organization
  WHERE PARENT_ID IS NULL

  UNION ALL

  -- RECURSIVE: Children
  SELECT o.ID, o.PARENT_ID, o.ORG_NAME,
         h.DEPTH + 1,
         h.FULL_PATH || ' / ' || o.ORG_NAME
  FROM cs_organization o
  JOIN org_hier h ON o.PARENT_ID = h.ID
  WHERE h.DEPTH < 50
)
SELECT ID, PARENT_ID, ORG_NAME, DEPTH, FULL_PATH
FROM org_hier
ORDER BY DEPTH, ORG_NAME;

Step-by-Step: Translating Your CONNECT BY

Step 1: Identify the START WITH Condition

Find the root nodes. Usually it's "PARENT_ID IS NULL" or "ORG_LEVEL = 'COMPANY'".

START WITH PARENT_ID IS NULL  → Write this as the WHERE in your anchor

Step 2: Identify the CONNECT BY PRIOR Condition

This is the join condition. "CONNECT BY PRIOR parent_col = child_col" means "child rows whose parent_col matches current parent_col".

CONNECT BY PRIOR ID = PARENT_ID
→ Write as: JOIN org_hier h ON o.PARENT_ID = h.ID

Step 3: Build the Anchor

SELECT from the table with the START WITH condition:

SELECT ID, PARENT_ID, ORG_NAME,
       1 AS DEPTH,
       TO_NVARCHAR(ORG_NAME) AS FULL_PATH
FROM cs_organization
WHERE PARENT_ID IS NULL

Step 4: Build the Recursive Branch

JOIN the table to the CTE, with the CONNECT BY condition and a depth limit:

SELECT o.ID, o.PARENT_ID, o.ORG_NAME,
       h.DEPTH + 1,
       h.FULL_PATH || ' / ' || o.ORG_NAME
FROM cs_organization o
JOIN org_hier h ON o.PARENT_ID = h.ID
WHERE h.DEPTH < 50

Step 5: Handle LEVEL and SYS_CONNECT_BY_PATH

• LEVEL: Add a DEPTH column. Start at 1 in anchor, increment by 1 in recursive branch.
• SYS_CONNECT_BY_PATH: Add a PATH column. Start with current value in anchor, concatenate with separator in recursive branch.

Advanced Pattern: Traversing Up (Parents → Root)

Sometimes you traverse up instead of down. Find the parent manager of a given employee:

SELECT ID, PARENT_ID, ORG_NAME, LEVEL
FROM cs_organization
START WITH ID = 12345  -- Start at a specific node
CONNECT BY PRIOR PARENT_ID = ID  -- Walk up: parent's parent_id matches current id;

WITH RECURSIVE org_hier (ID, PARENT_ID, ORG_NAME, DEPTH) AS (
  -- ANCHOR: Start at node 12345
  SELECT ID, PARENT_ID, ORG_NAME, 1
  FROM cs_organization
  WHERE ID = 12345

  UNION ALL

  -- RECURSIVE: Walk up (parent.id = h.parent_id)
  SELECT o.ID, o.PARENT_ID, o.ORG_NAME, h.DEPTH + 1
  FROM cs_organization o
  JOIN org_hier h ON o.ID = h.PARENT_ID
  WHERE h.DEPTH < 50
)
SELECT ID, PARENT_ID, ORG_NAME, DEPTH
FROM org_hier;

Handling Cycles: CONNECT BY NOCYCLE vs HANA Depth Limit

A cycle occurs when a node is its own ancestor (data corruption). Oracle handles this with CONNECT BY NOCYCLE:

SELECT ID, PARENT_ID, ORG_NAME
FROM cs_organization
START WITH PARENT_ID IS NULL
CONNECT BY NOCYCLE PRIOR ID = PARENT_ID;

In HANA, there's no NOCYCLE keyword. Instead, use a WHERE clause depth limit:

WITH RECURSIVE org_hier (...) AS (
  ...
  WHERE h.DEPTH < 50  -- Stops after 50 levels (prevents cycles)
)
...

NULL Handling in Hierarchies

Both databases handle NULLs in different ways:

• Oracle: Treats NULL PARENT_ID as a root node. Matches in CONNECT BY are NULL-safe (NULL = NULL returns FALSE, so no artificial parent-child links).
• HANA: Same behavior in joins (NULL doesn't match NULL). But NULL in LEVEL/DEPTH tracking can cause surprises.

Best practice: Ensure no NULL PARENT_IDs except at the root. Validate before migrating:

-- Orphan nodes: parent_id doesn't exist in any id
SELECT ID, PARENT_ID
FROM cs_organization
WHERE PARENT_ID IS NOT NULL
AND PARENT_ID NOT IN (SELECT ID FROM cs_organization);

Performance Considerations

Oracle CONNECT BY Performance

• Efficient for small-to-medium hierarchies (< 100k nodes).
• Works well even with shallow hierarchies (< 10 levels).

HANA Recursive CTE Performance

• Can be slow on large hierarchies. Each recursion level re-joins the table.
• Deep hierarchies (> 50 levels) are inefficient (you'll hit the depth limit anyway).
• Optimization: Index on PARENT_ID and ID improves join performance. Create them after migration.

Migration Checklist: Hierarchical Queries

1. Find all CONNECT BY queries: grep -i "CONNECT BY" *.sql
2. For each query, identify: START WITH condition, CONNECT BY PRIOR condition, use of LEVEL, use of SYS_CONNECT_BY_PATH, use of CONNECT BY NOCYCLE.
3. Build the anchor: SELECT with the START WITH condition, set DEPTH = 1.
4. Build the recursive branch: SELECT with JOIN and DEPTH < limit.
5. Add path tracking if needed: Concatenate path string in recursive branch.
6. Test on sample data first: Run both Oracle and HANA queries side-by-side on 1000-row sample. Validate results match.
7. Test on production data: Run on full Callidus hierarchy. Monitor execution time. Adjust depth limit if needed.
8. Audit for cycles: Check for corrupted hierarchies before migration.