Informix Error -26088: A recursive CTE must have at least one non-recursive sub-query and exactly one UNION ALL recursive sub-query.

Cause and resolution

A recursive CTE must have at least one non-recursive sub-query and exactly one UNION ALL recursive sub-query.

The query presented either did not contain any non-recursive sub-query (the only sub-query was recursive) or it contained two or more recursive sub-queries, or the recursive sub-query was joined with UNION instead of UNION ALL.

Each CTE (common table expression) in a WITH clause can define a recursive query. A recursive query starts with either one non-recursive sub-query or several non-recursive sub-queries joined by UNION or UNION ALL, and ends with exactly one recursive sub-query joined by UNION ALL. A recursive sub-query references the CTE being defined.

Oninit® Troubleshooting Guidance

Reasons / Common Causes

-26088 fires when a recursive common table expression (CTE) doesn't follow the required non-recursive/recursive sub-query structure — per the official guidance.

No non-recursive sub-query at all (the only sub-query is recursive), per the official guidance — the first named case.
Two or more recursive sub-queries, per the official guidance — the second named case.
The recursive sub-query joined with UNION instead of UNION ALL, per the official guidance — the third named case.

Solutions / Resolution

Give the CTE one or more non-recursive sub-queries (joined by UNION/UNION ALL) followed by exactly one recursive sub-query joined with UNION ALL, per the official guidance — the recursive sub-query must reference the CTE being defined.

Examples

A valid recursive CTE

WITH RECURSIVE org_chart (emp_id, mgr_id, lvl) AS (
  SELECT emp_id, mgr_id, 1 FROM employees WHERE mgr_id IS NULL
  UNION ALL
  SELECT e.emp_id, e.mgr_id, o.lvl + 1
    FROM employees e JOIN org_chart o ON e.mgr_id = o.emp_id
)
SELECT * FROM org_chart;

Diagnostic Checks

Check the CTE for at least one non-recursive sub-query and exactly one recursive sub-query joined with UNION ALL, and restructure it if not.

Related Errors / Related Topics

No closely related error codes are cross-referenced for -26088 in this set yet.

A recursive CTE didn't follow the required non-recursive-then-UNION ALL-recursive structure — restructure it.