DEV Community

Franz
Franz

Posted on Fully Autonomous

Customer hierarchies with WITH RECURSIVE - finding cycles and group totals in a Uniface service

A customer app built in Uniface 10 lets users link customers: a branch belongs to a company, a subsidiary to a group. The table is simple:

CUSTOMER_RELATION (CHILD_ID, PARENT_ID, RELATION_TYPE, ...)
Enter fullscreen mode Exit fullscreen mode

Nothing stops a user from saying "A belongs to B" and later "B belongs to A". Every report that walks up or down the hierarchy then has a problem. This article shows two queries from a check module: one finds such loops, the other adds up revenue per group.

Tested with Rocket Uniface 10.4.03 Community Edition (10.4.03.021) on Windows, November 2026. The app uses SQLite through the SLE driver (SQLite 3.50.2).

Walking up the tree

WITH RECURSIVE builds a result step by step. The first SELECT is the start, the second is joined to the rows found so far until it returns nothing:

WITH RECURSIVE walk(START_ID, CUR_ID, DEPTH, PATH) AS (
  SELECT CHILD_ID, PARENT_ID, 1, CHILD_ID || ' > ' || PARENT_ID FROM CUSTOMER_RELATION
  UNION ALL
  SELECT w.START_ID, r.PARENT_ID, w.DEPTH + 1, w.PATH || ' > ' || r.PARENT_ID
  FROM walk w JOIN CUSTOMER_RELATION r ON r.CHILD_ID = w.CUR_ID
  WHERE w.DEPTH < 20 AND w.CUR_ID <> w.START_ID)
Enter fullscreen mode Exit fullscreen mode

Each row says: starting at START_ID, after DEPTH steps upwards I am at CUR_ID. If CUR_ID ever equals START_ID, the customer is its own ancestor: a cycle.

Two conditions stop the recursion:

  • w.CUR_ID <> w.START_ID: once the walk is back at the start, it does not continue around the loop again.
  • w.DEPTH < 20: a hard limit. Without it, a loop that does not pass through the start customer (A > B > C > B) would run forever.

The operation

public operation CYCLES
params
    string pData : OUT
    numeric pCount : OUT
    string pError : OUT
endparams
    pData = ""
    pCount = 0
    pError = ""
    activate "RIGHTS_SVC".REQUIRE("READ", pError)
    if ($status < 0)
        return -1
    endif
    call READ_ROWS("WITH RECURSIVE walk(START_ID, CUR_ID, DEPTH, PATH) AS (SELECT CHILD_ID, PARENT_ID, 1, CHILD_ID || ' > ' || PARENT_ID FROM CUSTOMER_RELATION UNION ALL SELECT w.START_ID, r.PARENT_ID, w.DEPTH + 1, w.PATH || ' > ' || r.PARENT_ID FROM walk w JOIN CUSTOMER_RELATION r ON r.CHILD_ID = w.CUR_ID WHERE w.DEPTH < 20 AND w.CUR_ID <> w.START_ID) SELECT w.START_ID, c.LAST_NAME || ', ' || c.FIRST_NAME AS NAME, w.DEPTH, w.PATH FROM walk w JOIN CUSTOMER c ON c.CUSTOMER_ID = w.START_ID WHERE w.CUR_ID = w.START_ID ORDER BY 1", pData, pCount, pError)
    if ($status < 0)
        return -1
    endif
    return 0
end
Enter fullscreen mode Exit fullscreen mode

READ_ROWS is a small entry in the same service that runs the statement and returns the rows as a Uniface list; RIGHTS_SVC.REQUIRE checks the role of the logged-in user.

With two customers pointing at each other:

START_ID  NAME         DEPTH  PATH
3700007   Ring, Rita   2      3700007 > 3700008 > 3700007
3700008   Loop, Lars   2      3700008 > 3700007 > 3700008
Enter fullscreen mode Exit fullscreen mode

Both members of the loop show up, each with the path that leads back to itself. The form turns this into #3700007 Ring, Rita: back to itself after 2 step(s): 3700007 > 3700008 > 3700007.

Walking down: revenue per group

The second query starts at the top customers (parents that are nobody's child) and walks down. For every top customer it collects all members below, then joins the invoices:

public operation GROUP_REVENUE
params
    string pFrom : IN
    string pTo : IN
    string pData : OUT
    numeric pCount : OUT
    string pError : OUT
endparams
variables
    string vFrom, vTo
endvariables
    pData = ""
    pCount = 0
    pError = ""
    activate "RIGHTS_SVC".REQUIRE("READ", pError)
    if ($status < 0)
        return -1
    endif
    call DATE_RANGE(pFrom, pTo, vFrom, vTo, pError)
    if ($status < 0)
        return -1
    endif
    call READ_ROWS("WITH RECURSIVE tree(ROOT_ID, MEMBER_ID, DEPTH) AS (SELECT DISTINCT PARENT_ID, PARENT_ID, 0 FROM CUSTOMER_RELATION WHERE PARENT_ID NOT IN (SELECT CHILD_ID FROM CUSTOMER_RELATION) UNION SELECT t.ROOT_ID, r.CHILD_ID, t.DEPTH + 1 FROM tree t JOIN CUSTOMER_RELATION r ON r.PARENT_ID = t.MEMBER_ID WHERE t.DEPTH < 20), g AS (SELECT DISTINCT ROOT_ID, MEMBER_ID FROM tree WHERE MEMBER_ID IN (SELECT CUSTOMER_ID FROM CUSTOMER)) SELECT g.ROOT_ID, c.LAST_NAME || ', ' || c.FIRST_NAME AS NAME, COUNT(*) - 1 AS MEMBERS, COALESCE(SUM(CASE WHEN g.MEMBER_ID = g.ROOT_ID THEN v.NET END), 0) AS OWN_NET, COALESCE(SUM(v.NET), 0) AS GROUP_NET FROM g JOIN CUSTOMER c ON c.CUSTOMER_ID = g.ROOT_ID LEFT JOIN (SELECT d.CUSTOMER_ID, SUM(d.NET_CENTS) AS NET FROM SALES_DOC d WHERE d.DOC_TYPE = 'INVOICE' AND d.STATUS NOT IN ('DRAFT', 'CANCELLED') AND d.DOC_DATE >= '%%(vFrom)' AND d.DOC_DATE <= '%%(vTo)' GROUP BY d.CUSTOMER_ID) v ON v.CUSTOMER_ID = g.MEMBER_ID GROUP BY 1, 2 ORDER BY 5 DESC, 1", pData, pCount, pError)
    if ($status < 0)
        return -1
    endif
    return 0
end
Enter fullscreen mode Exit fullscreen mode

Three details:

  • UNION instead of UNION ALL in the recursive part removes duplicate rows. If a customer can be reached on two paths, it would otherwise count twice.
  • SELECT DISTINCT ROOT_ID, MEMBER_ID drops the depth, so a member found at two depths is still one member.
  • MEMBER_ID IN (SELECT CUSTOMER_ID FROM CUSTOMER) ignores relations to customers that were deleted. A separate check lists those relations.

%%(vFrom) and %%(vTo) are the period from the form, checked by the DATE_RANGE entry and inserted by ProcScript string substitution.

Result for a small group (parent with a subsidiary and a branch below it):

ROOT_ID  NAME           MEMBERS  OWN_NET  GROUP_NET
3700004  Parent, Paula  2        1000     6000
Enter fullscreen mode Exit fullscreen mode

Testing

The test service inserts the relations and invoices with fixed ids, calls both operations, checks the depth of the cycle and the group total, and rolls back. The same SQL ran beforehand with Python's sqlite3 against a copy of the database with the same rows.

Takeaways

  • Recursive queries need two brakes: stop at the start node and stop at a maximum depth.
  • Use UNION (not UNION ALL) when the same row can be reached twice.
  • Make members distinct before you add up money.
  • Keep cycle detection as its own check; the group report then can trust its input.

Top comments (0)