A common table expression (CTE) lets you name an intermediate result and build a query in readable steps. This guide covers the WITH clause, chaining several CTEs, using CTEs in UPDATE and DELETE statements, recursive CTEs for hierarchical data such as bills of materials and organisation charts, cycle detection, using a CTE in an RPG embedded SQL cursor, and performance considerations on IBM i.
What Is a CTE and Why Use One?
A Common Table Expression (CTE) is a named, temporary result set defined with the WITH clause at the start of a SELECT, INSERT, UPDATE, or DELETE statement. The CTE exists only for the duration of the single SQL statement in which it is defined — it is not stored as a view or a table.
CTEs improve SQL maintainability in three concrete ways:
- Readability — a complex nested subquery becomes a named, readable step; future developers can read the WITH clause to understand each logical stage of the query
- Reuse within a statement — the same CTE can be referenced multiple times in the main query without repeating the subquery logic
- Recursive traversal — a CTE can reference itself, which a subquery or derived table cannot, so it can traverse trees and graphs
A CTE is not automatically better than a derived table or a view for performance — the DB2 for i query optimiser (SQE) may materialise or inline a CTE depending on complexity. Understanding when CTEs help and when they are neutral is part of writing good SQL on IBM i.
Basic CTE Syntax
The simplest form: a single CTE used to avoid repeating a subquery:
-- Find customers whose total order value exceeds their credit limit
-- Without CTE: nested subquery in WHERE clause is hard to read
-- With CTE: each step is named and readable
WITH OrderTotals AS (
SELECT
CUSNO,
SUM(ORDAMT) AS total_ordered
FROM ORDLIB.ORDMST
WHERE ORDSTS NOT IN ('X', 'C') -- Exclude cancelled orders
GROUP BY CUSNO
)
SELECT
C.CUSNO,
C.CUSNAM,
C.CRDLMT,
OT.total_ordered,
OT.total_ordered - C.CRDLMT AS over_limit_amount
FROM ORDLIB.CUSTMST C
JOIN OrderTotals OT ON OT.CUSNO = C.CUSNO
WHERE OT.total_ordered > C.CRDLMT
ORDER BY over_limit_amount DESC;
Chaining Multiple CTEs
Multiple CTEs can be chained in a single WITH clause, separated by commas. Each CTE can reference any CTE defined before it in the chain. This is the primary technique for breaking a complex multi-step analytical query into readable stages:
-- Monthly sales summary with rank and running total
-- Step 1: raw order totals by month and region
-- Step 2: rank each region within each month
-- Step 3: running total across months
WITH MonthlySales AS (
SELECT
YEAR(ORDDAT) AS sale_year,
MONTH(ORDDAT) AS sale_month,
REGION,
SUM(ORDAMT) AS monthly_total
FROM SALESLIB.ORDMST
WHERE ORDSTS = 'C' -- Completed orders only
AND ORDDAT >= DATE('2026-01-01')
GROUP BY YEAR(ORDDAT), MONTH(ORDDAT), REGION
),
RankedSales AS (
SELECT
sale_year,
sale_month,
REGION,
monthly_total,
RANK() OVER (
PARTITION BY sale_year, sale_month
ORDER BY monthly_total DESC
) AS region_rank
FROM MonthlySales
),
RunningTotal AS (
SELECT
sale_year,
sale_month,
REGION,
monthly_total,
region_rank,
SUM(monthly_total) OVER (
PARTITION BY REGION
ORDER BY sale_year, sale_month
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
) AS running_total
FROM RankedSales
)
SELECT
sale_year,
sale_month,
REGION,
monthly_total,
region_rank,
DECIMAL(running_total, 15, 2) AS running_total
FROM RunningTotal
WHERE region_rank <= 3 -- Top 3 regions per month only
ORDER BY sale_year, sale_month, region_rank;
This query has three logical stages — aggregation, ranking, running total — each expressed as a named CTE. The final SELECT is a simple filter and sort. Without CTEs, this would require three levels of nested derived tables, which are significantly harder to read and debug.
CTEs in UPDATE and DELETE Statements
CTEs can be used in DML statements (UPDATE, DELETE, INSERT) on DB2 for i. This is useful when the filter condition for the DML requires a complex multi-table join or aggregation that is cleaner as a CTE than as a correlated subquery.
-- Mark orders as 'H' (Hold) for customers who have exceeded their credit limit
WITH OverLimitCustomers AS (
SELECT
C.CUSNO
FROM ORDLIB.CUSTMST C
JOIN (SELECT CUSNO, SUM(ORDAMT) AS total_open
FROM ORDLIB.ORDMST
WHERE ORDSTS = 'O'
GROUP BY CUSNO) OT ON OT.CUSNO = C.CUSNO
WHERE OT.total_open > C.CRDLMT
)
UPDATE ORDLIB.ORDMST
SET ORDSTS = 'H',
HLDDAT = CURRENT DATE
WHERE ORDSTS = 'O'
AND CUSNO IN (SELECT CUSNO FROM OverLimitCustomers);
-- Delete old audit log rows older than 90 days, but keep at least
-- one row per user as the most recent record (using CTE to identify keepers)
WITH LatestAudit AS (
SELECT
USER_NAME,
MAX(AUDIT_TIMESTAMP) AS latest_ts
FROM APPLIB.AUDITLOG
GROUP BY USER_NAME
)
DELETE FROM APPLIB.AUDITLOG AL
WHERE AL.AUDIT_TIMESTAMP < CURRENT_TIMESTAMP - 90 DAYS
AND NOT EXISTS (
SELECT 1
FROM LatestAudit LA
WHERE LA.USER_NAME = AL.USER_NAME
AND LA.latest_ts = AL.AUDIT_TIMESTAMP
);
Recursive CTEs — Traversing Hierarchical Data
A recursive CTE references itself, which lets a single query walk a tree or graph stored as parent-child rows. On Db2 for i you write it with the ordinary WITH clause: a column list after the CTE name, then an initialization fullselect and an iterative fullselect joined by UNION ALL. The documented syntax has no RECURSIVE keyword. The two parts are:
- Anchor member (IBM calls it the initialization fullselect) — the non-recursive starting point, the root of the tree
- Recursive member (the iterative fullselect) — the part that references the CTE itself to walk one level deeper
-- Employee organisational hierarchy
-- Table: APPLIB.EMPORG (EMPNO, EMPNAM, MGRNO, DEPT, SALARY)
-- MGRNO references EMPNO of the employee's manager; top-level managers have MGRNO = 0
WITH OrgTree (
EMPNO, EMPNAM, MGRNO, DEPT, SALARY, LEVEL, PATH
) AS (
-- Anchor: start with the CEO (no manager)
SELECT
EMPNO,
EMPNAM,
MGRNO,
DEPT,
SALARY,
0 AS LEVEL,
CAST(EMPNAM AS VARCHAR(500)) AS PATH
FROM APPLIB.EMPORG
WHERE MGRNO = 0
UNION ALL
-- Recursive member: join each employee to their manager row
SELECT
E.EMPNO,
E.EMPNAM,
E.MGRNO,
E.DEPT,
E.SALARY,
OT.LEVEL + 1,
OT.PATH CONCAT ' > ' CONCAT E.EMPNAM
FROM APPLIB.EMPORG E
JOIN OrgTree OT ON OT.EMPNO = E.MGRNO
)
SELECT
LEVEL,
REPEAT(' ', LEVEL) CONCAT EMPNAM AS indented_name,
DEPT,
DECIMAL(SALARY, 11, 2) AS salary,
PATH AS reporting_chain
FROM OrgTree
ORDER BY PATH;
The LEVEL pseudo-column (computed manually here as an incrementing integer) indicates depth in the tree. The PATH column builds a human-readable breadcrumb of the reporting chain at each level.
A recursive CTE returns rows breadth-first by default. To order the result depth-first (each parent followed by its children), add a SEARCH clause between the CTE and the final query, for example SEARCH DEPTH FIRST BY EMPNO SET ordcol, and order the final SELECT by ordcol.
BOM Explosion with Recursive SQL
Bill of Materials (BOM) explosion is the classic recursive SQL use case in manufacturing IBM i applications. The BOM table stores a parent-component relationship; the recursive CTE walks the tree to find all components at every level needed to build a top-level item.
-- BOM table: APPLIB.BOMLNK (PARITEM, CMPITEM, QTYPER, UOFM)
-- PARITEM = parent item number, CMPITEM = component item number
-- QTYPER = quantity of component per one unit of parent
WITH BOMExplosion (
PARITEM, CMPITEM, QTYPER, UOFM, LEVEL, EXTENDED_QTY, PATH
) AS (
-- Anchor: direct components of the top-level item (part P10050)
SELECT
PARITEM,
CMPITEM,
QTYPER,
UOFM,
1 AS LEVEL,
QTYPER AS EXTENDED_QTY,
CAST(PARITEM AS VARCHAR(200)) AS PATH
FROM APPLIB.BOMLNK
WHERE PARITEM = 'P10050'
UNION ALL
-- Recursive: components of each component found so far
SELECT
B.PARITEM,
B.CMPITEM,
B.QTYPER,
B.UOFM,
BE.LEVEL + 1,
BE.EXTENDED_QTY * B.QTYPER, -- Multiply quantities down the tree
BE.PATH CONCAT ' > ' CONCAT B.PARITEM
FROM APPLIB.BOMLNK B
JOIN BOMExplosion BE ON BE.CMPITEM = B.PARITEM
WHERE BE.LEVEL < 10 -- Safety stop: max 10 levels deep
)
SELECT
LEVEL,
REPEAT(' ', LEVEL) CONCAT CMPITEM AS component,
DECIMAL(EXTENDED_QTY, 11, 4) AS extended_qty,
UOFM,
PATH AS bom_path
FROM BOMExplosion
ORDER BY PATH, LEVEL;
The WHERE BE.LEVEL < 10 clause is a guard, and it follows IBM’s guidance that a recursive CTE must be written so that its recursion ends: carry an integer column that the recursive member increments by a constant, and test it in the recursive member’s WHERE clause with a predicate such as counter_col < constant or counter_col < :hostvar. A recursive CTE without such a guard can loop indefinitely on circular data (item A requires item B, which requires item A), and Db2 for i can warn when the pattern is missing. For production BOM queries use a level limit, cycle detection, or both.
Cycle Detection in Recursive CTEs
Circular data makes a recursive CTE run forever unless something stops it. Db2 for i has a built-in CYCLE clause for this. You name the column or columns that carry the parent-child relationship; when a new row repeats a value already on its own path, Db2 for i marks the row and stops recursing down that branch. The clause goes after the CTE definition and before the final SELECT:
WITH SafeBOM (PARITEM, CMPITEM, QTYPER, LEVEL) AS (
SELECT PARITEM, CMPITEM, QTYPER, 1
FROM APPLIB.BOMLNK
WHERE PARITEM = 'P10050'
UNION ALL
SELECT B.PARITEM, B.CMPITEM, B.QTYPER, SB.LEVEL + 1
FROM APPLIB.BOMLNK B
JOIN SafeBOM SB ON SB.CMPITEM = B.PARITEM
WHERE SB.LEVEL < 15
)
CYCLE CMPITEM SET IS_CYCLE TO '1' DEFAULT '0'
SELECT PARITEM, CMPITEM, QTYPER, LEVEL, IS_CYCLE
FROM SafeBOM;
-- IS_CYCLE = '1' marks the row where a circular reference was found
If you want the path itself in the result, you can also carry a path string and test it with LOCATE yourself:
WITH SafeBOM (
PARITEM, CMPITEM, QTYPER, LEVEL, PATH, IS_CYCLE
) AS (
-- Anchor
SELECT
PARITEM, CMPITEM, QTYPER,
1,
CAST(PARITEM AS VARCHAR(1000)),
CASE WHEN LOCATE(PARITEM, CAST(PARITEM AS VARCHAR(1000))) > 0
THEN 1 ELSE 0 END
FROM APPLIB.BOMLNK
WHERE PARITEM = 'P10050'
UNION ALL
-- Recursive member — stop if cycle detected
SELECT
B.PARITEM, B.CMPITEM, B.QTYPER,
SB.LEVEL + 1,
SB.PATH CONCAT '>' CONCAT B.PARITEM,
CASE WHEN LOCATE('>' CONCAT B.PARITEM CONCAT '>', '>' CONCAT SB.PATH CONCAT '>') > 0
THEN 1 ELSE 0 END
FROM APPLIB.BOMLNK B
JOIN SafeBOM SB ON SB.CMPITEM = B.PARITEM AND SB.IS_CYCLE = 0
WHERE SB.LEVEL < 15
)
SELECT PARITEM, CMPITEM, QTYPER, LEVEL, IS_CYCLE, PATH
FROM SafeBOM
ORDER BY PATH;
-- IS_CYCLE = 1 rows indicate where a circular reference exists in the BOM data
CTE Performance Considerations on IBM i
CTEs on DB2 for i are handled by the SQL Query Engine (SQE), which decides independently whether to materialise the CTE (evaluate it once and store the result) or inline it (substitute the CTE definition everywhere it is referenced, like a macro). Understanding this matters for performance:
- Single-reference CTEs are usually inlined — if a CTE is referenced only once, SQE typically substitutes its definition into the outer query and optimises the combined query as a whole. The CTE has no performance overhead in this case.
- Multi-reference CTEs may be materialised — if a CTE is referenced two or more times in the main query, SQE may evaluate it once and store the intermediate result in a temporary table, then join against that result twice. This avoids repeating the inner query but adds a sort/spool step.
- Recursive CTEs are always materialised — the iterative nature of recursion requires materialisation; SQE cannot inline a recursive CTE.
- Use Visual Explain to check materialisation — in ACS (Access Client Solutions) Run SQL Scripts, press F13 or use the Visual Explain button after running a query to see whether each CTE was inlined or materialised, and whether indexes were used at each step.
To see what the optimizer actually did with a particular CTE query, capture it with the database monitor (STRDBMON) or take a plan cache snapshot in the ACS SQL Performance Center, then open the statement in Visual Explain.
CTEs vs Views vs Derived Tables
| Feature | CTE (WITH) | SQL View | Derived Table (inline) |
|---|---|---|---|
| Scope | Single statement | Persistent, reusable | Single statement |
| Named reference | Yes | Yes (via QSYS2 or library) | No (anonymous) |
| Recursive support | Yes (self-referencing CTE) | Yes (recursive view) | No |
| Multi-use in query | Yes | Yes | No (must repeat) |
| Performance hint | SQE decides materialise/inline | SQE merges with outer query | Always inlined |
| Maintenance | In SQL only | DDL object in library | In SQL only |
Use a CTE when the logic is needed only within one query and is too complex to read as a nested derived table. Use a view when the same query structure is needed across multiple programs or SQL scripts. Use a derived table (inline subquery) for simple, short filters that do not benefit from naming.
Using a CTE in an RPG Embedded SQL Cursor
A CTE is part of the SELECT statement, so the statement behind an embedded SQL cursor can begin with WITH. The cursor works like any other: declare it, open it, fetch in a loop, and close it. The example reads the components under one parent item, with the recursion guard supplied from a host variable:
**free
ctl-opt dftactgrp(*no) actgrp(*new);
dcl-s parentItem char(15) inz('P10050');
dcl-s maxLevel int(10) inz(10);
dcl-s cmpItem char(15);
dcl-s itemLevel int(10);
dcl-s extQty packed(15:3);
dcl-s sqlCode int(10);
exec sql
declare bomCsr cursor for
with bomTree (cmpitem, itemlevel, extqty) as (
select cmpitem, 1, qtyper
from applib.bomlnk
where paritem = :parentItem
union all
select b.cmpitem, t.itemlevel + 1, t.extqty * b.qtyper
from applib.bomlnk b
join bomTree t on t.cmpitem = b.paritem
where t.itemlevel < :maxLevel
)
select cmpitem, itemlevel, extqty
from bomTree
order by itemlevel, cmpitem
for read only;
exec sql open bomCsr;
exec sql fetch next from bomCsr into :cmpItem, :itemLevel, :extQty;
dow sqlCode = 0;
// use cmpItem, itemLevel and extQty here
exec sql fetch next from bomCsr into :cmpItem, :itemLevel, :extQty;
enddo;
exec sql close bomCsr;
*inlr = *on;
A few points about this pattern:
- Declare the host variables before the
DECLARE CURSORstatement. - The whole statement, including the
WITHclause, is the cursor’s select-statement, and it can reference host variables, including in the recursion guard. - Add
FOR READ ONLYwhen the cursor is never used for a positioned UPDATE or DELETE. IBM recommends this, and it also lets the system block rows for more efficient processing. - Keep the recursion guard, because a cursor does not make a runaway recursive CTE any safer.
Practical CTE Patterns for IBM i Applications
-- Pattern: CTE to identify duplicate rows before deletion
WITH DuplicateOrders AS (
SELECT
ORDNO,
ROW_NUMBER() OVER (
PARTITION BY CUSNO, ORDDAT, ORDAMT
ORDER BY ORDNO ASC
) AS row_num
FROM ORDLIB.ORDMST
)
DELETE FROM ORDLIB.ORDMST
WHERE ORDNO IN (
SELECT ORDNO
FROM DuplicateOrders
WHERE row_num > 1 -- Keep only the first occurrence
);
-- Pattern: CTE for a paginated query result in an RPG program
-- Using OFFSET/FETCH for paging (IBM i 7.2+)
WITH PagedOrders AS (
SELECT
ORDNO, CUSNO, CUSNAM, ORDAMT, ORDDAT, ORDSTS,
ROW_NUMBER() OVER (ORDER BY ORDDAT DESC, ORDNO DESC) AS rn
FROM ORDLIB.ORDMST O
JOIN ORDLIB.CUSTMST C ON C.CUSNO = O.CUSNO
WHERE O.ORDSTS = 'O'
)
SELECT ORDNO, CUSNO, CUSNAM, ORDAMT, ORDDAT, ORDSTS
FROM PagedOrders
WHERE rn BETWEEN :startRow AND :endRow
ORDER BY rn;
-- :startRow and :endRow are host variables from the RPG program
-- Page 1: startRow=1, endRow=20; Page 2: startRow=21, endRow=40