Programming & Db2
Your SQLRPGLE Cursor Has More Rows. Why Did the Loop Stop?
An optional lookup can overwrite SQLCOD and stop a cursor loop early. Follow a five-row SQLRPGLE example, identify which statement returned +100, and give FETCH its own status handling.
The query returns five orders. The RPG loop processes two, closes the cursor, and returns. Before changing the query, check which SQL statement last updated the status your loop is testing.
An embedded SQL loop can stop early even when the cursor has more rows available. One way this happens is easy to overlook: a statement inside the loop returns +100, and the loop treats that result as the end of the cursor.
The useful debugging question is: which statement returned that status?
This walkthrough uses a deliberately small, synthetic example. The baseline was compiled and run on IBM i, confirming the behaviour described here: the broken program processes two orders, while the corrected program processes all five. The example demonstrates the bug in isolation; it is not a captured production incident.
SQLCOD is not an end-of-cursor flag
Embedded SQL reports statement results through the SQL communication area, or SQLCA. In ILE RPG, SQLCOD is the short name for SQLCODE; SQLSTT is the short name for SQLSTATE. The status describes the SQL statement that produced it. It is not stored separately for each cursor. IBM SQLCODE and SQLSTATE concepts · SQLCA field descriptions
That makes the following distinction essential:
| Statement | What +100 means in this example |
|---|---|
A single-row FETCH NEXT |
No next row was returned; the cursor reached the end of its result. |
A singleton SELECT ... INTO |
No row matched the lookup. |
Both can return SQLSTATE '02000'. A missing lookup row says nothing about whether the cursor has another order. IBM documents both cases under SQL0100. IBM SQL messages
Five orders, one missing optional lookup
Our fixture has two tables: EOICURSOR.ORDERS and EOICURSOR.PROMOS.
| Order | Promotion code | Lookup result |
|---|---|---|
| 1 | SAVE10 | Found: discount 10.00 |
| 2 | NONE | No matching promotion |
| 3 | SAVE10 | Found: discount 10.00 |
| 4 | SAVE10 | Found: discount 10.00 |
| 5 | SAVE10 | Found: discount 10.00 |
For this example, a missing promotion is allowed and means zero discount. That is an explicit business rule for the fixture. In another application, missing reference data might require rejecting the order or stopping the batch.
The cursor reads orders by ORDER_ID. The program then looks up the promotion for each fetched order. The promotion key is unique, and the selected columns are non-nullable, keeping the example focused on statement status.
The loop that stops too soon
Here is the relevant part of the broken program. Declarations, the checked OPEN, and cleanup are included in the downloadable source.
// Deliberately incorrect outer loop condition.
dou SQLCOD = 100;
exec sql fetch next from C1 into :orderId, :promoCode;
if SQLCOD = 100;
leave;
elseif SQLCOD <> 0;
// Report unexpected FETCH status, then stop.
leave;
endif;
discount = 0;
exec sql select DISCOUNT into :discount
from EOICURSOR.PROMOS
where PROMO_CODE = :promoCode;
if SQLCOD = 100;
discount = 0; // Missing optional promotion is allowed.
elseif SQLCOD <> 0;
// Report unexpected lookup status, then stop.
leave;
endif;
processed += 1;
// Display this order. No further SQL before ENDDO.
enddo;
RPG evaluates the DOU condition at the bottom of the loop. After order 2, SQLCOD is 100 because the promotion lookup found no row. Assigning zero to discount does not change that status.
The program counts order 2, reaches ENDDO, and exits. It never asks the cursor for order 3.
| Step | SQL statement just executed | SQLCOD | Consequence |
|---|---|---|---|
| Fetch order 1 | FETCH NEXT |
0 | Process the fetched row. |
| Look up SAVE10 | SELECT INTO |
0 | Bottom-of-loop test allows another iteration. |
| Fetch order 2 | FETCH NEXT |
0 | The cursor successfully returned a row. |
| Look up NONE | SELECT INTO |
100 | Lookup has no match. |
Evaluate DOU |
No new SQL statement | 100 | Exit before fetching order 3. |
This depends on the exact statement order. A loop with another FETCH immediately before its continuation test will have a different status at that point. An SQL lookup inside a cursor loop is not enough, by itself, to cause this bug. The failure is checking one statement’s result as though it belonged to another.
Give FETCH control over cursor exhaustion
The corrected program uses an explicit loop and handles the fetch result immediately. It saves the code, state, and warning flag in ordinary RPG variables before running any other SQL.
dou *off;
exec sql fetch next from C1 into :orderId, :promoCode;
fetchCode = SQLCOD;
fetchState = SQLSTT;
fetchWarn = %subst(SQLWRN:1:1);
if fetchCode = 100;
reachedEnd = *on;
leave;
elseif fetchCode <> 0 or fetchState <> '00000' or fetchWarn = 'W';
// Report saved FETCH status; this is not normal completion.
hadError = *on;
leave;
endif;
discount = 0;
exec sql select DISCOUNT into :discount
from EOICURSOR.PROMOS
where PROMO_CODE = :promoCode;
lookupCode = SQLCOD;
lookupState = SQLSTT;
lookupWarn = %subst(SQLWRN:1:1);
if lookupCode = 100;
discount = 0;
elseif lookupCode <> 0 or lookupState <> '00000' or lookupWarn = 'W';
// Report saved lookup status and stop processing.
hadError = *on;
leave;
endif;
processed += 1;
// Display this order, then fetch the next one.
enddo;
With this structure, the missing promotion affects only order 2’s discount. Iteration continues until a subsequent FETCH reports end-of-data, or an unexpected status causes an explicit stop.
Saving the status also makes its ownership visible during debugging: fetchCode belongs to the fetch, and lookupCode belongs to the lookup. Check the result before calling a helper that may run more SQL against the same SQLCA.
These examples deliberately stop on unexpected warnings as well as errors. IBM notes that SQL warning indicators can be set even with a zero SQLCODE. A production application should define which warnings it accepts rather than treating all non-negative results as interchangeable. SQLCA field descriptions
Preserve the distinction between “finished” and “stopped”
The complete programs check OPEN and CLOSE too. They keep an explicit reachedEnd flag and an error flag, so cursor cleanup cannot turn an earlier failure into apparent success.
A successful CLOSE can replace the SQL status you were investigating. Preserve the relevant statement status before cleanup, and report completion from the program’s own outcome flags.
The demonstration uses DSPLY and a counter as its processing step. It does not update orders. Its normal RPG return is not a production success contract: a real caller or scheduler needs an explicit way to distinguish a completed batch from a failed one.
Also handle the lookup’s output deliberately. Initialize or assign the fallback value on the no-match path, and consume lookup values only after accepting the lookup result. Otherwise, the program can accidentally reuse a previous order’s value. A missing optional promotion is not the same as a valid zero-valued promotion, even if this example chooses the same discount for both.
Reproduce the example
The download contains a setup script and both complete SQLRPGLE programs:
Use an unused development schema name. Run the setup once, then compile the examples with CRTSQLRPGI using the instructions. The programs use COMMIT(*NONE) for this read-only demonstration; the example does not prescribe transaction settings for business updates.
The confirmed baseline result is:
| Program | Orders displayed and counted | Completion message |
|---|---|---|
| CURBAD | 1 and 2 | Stopped before FETCH reported end-of-data |
| CURGOOD | 1 through 5 | Complete: FETCH reached end-of-data |
The instructions also include additional test cases: moving the missing lookup to order 1, making every lookup match, and selecting an empty result. These are suggested checks for your environment; they are separate from the confirmed five-order baseline above.
Three changes that do not solve the underlying problem
Changing the loop to check SQLSTATE instead. If the test still follows the lookup, it still observes the lookup’s result. The ownership problem remains.
Resetting SQLCOD to zero after the lookup. That overwrites diagnostic information to satisfy a loop condition. Prefer a loop whose continuation does not depend on an unrelated statement’s status.
Moving FETCH and ignoring all other statuses. That may change this particular symptom, but errors or warnings from the lookup still need their own handling.
For a simple optional lookup like this one, a LEFT JOIN may also remove the second SQL statement. Evaluate that separately: uniqueness, missing-row handling, and the intended processing behaviour must stay correct.
This article covers one row per forward fetch. Multiple-row fetches need row-count and partial-result handling; do not copy the early +100 exit into a rowset loop without accounting for returned rows. IBM SQL programming guide
When a cursor loop stops unexpectedly, put a breakpoint immediately after each SQL statement and identify which result the continuation test sees. In this example, order 3 is still waiting. The loop exits on the result of the promotion lookup for order 2.
References
Documentation and supporting sources for this article.

Comments
Share your thoughts, questions, or real-world IBM i experiences related to this article.