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, ...)
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)
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
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
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
Three details:
-
UNIONinstead ofUNION ALLin 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_IDdrops 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
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(notUNION 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)