THE MIDNIGHT CODE REVIEW / CASE 02
The cursor that
quit early.
Five orders. One missing promotion. A loop meant to process them all.
Follow the SQLRPGLE program to its next iteration.
The SQLRPGLE loop under review
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;A nudge, without the answer
DOU tests its condition at the bottom. Which SQL statement most recently set SQLCOD when order 2 reaches ENDDO?
THE REVEAL
Two orders counted. Three still waiting.
FETCH successfully returns order 2. Its optional promotion lookup finds no row and sets SQLCOD to +100. Setting discount = 0 does not reset that SQL status.
The program increments processed to 2. At ENDDO, the condition SQLCOD = 100 is true, so the loop exits before fetching order 3.
| Step | SQLCOD | What happens |
|---|---|---|
| FETCH order 2 | 0 | A row is available; processing continues. |
| SELECT promo NONE | +100 | No matching promotion row. |
| discount = 0 | +100 | An RPG assignment leaves SQLCA unchanged. |
| processed += 1 | +100 | Order 2 is counted. |
| ENDDO tests SQLCOD = 100 | +100 | Exit. No FETCH for order 3 occurs. |
In this example, +100 from FETCH means no next row; +100 from SELECT INTO means no lookup match. The cursor has not failed. The loop is using the lookup’s result to decide whether the cursor is finished.
Let FETCH decide when the cursor is exhausted.
Use an explicit loop, save each statement’s status immediately, and handle it before running more SQL. A missing optional lookup can then follow its own business rule.
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;The corrected program processes orders 1 through 5. Its next FETCH returns +100 and sets reachedEnd. Unexpected fetch or lookup statuses still stop processing through the explicit error path.
Would SQLSTATE fix the loop?
Changing the bottom test to SQLSTT = '02000' has the same ownership problem. Clearing SQLCOD manually also hides the diagnostic instead of giving each statement its own handling.
Run the complete programs.
This case uses the existing article’s five-order fixture and source downloads. The programs read, display, and count synthetic orders. The setup script creates a schema and tables; follow the build instructions in an unused development schema.
- Original SQLRPGLE program
- Corrected SQLRPGLE program
- Fixture setup SQL
- Build instructions and validation cases
Baseline: The linked article records the author-confirmed IBM i run: CURBAD counts two orders; CURGOOD counts all five. This challenge reuses that baseline and does not execute RPG in the browser.
Would this pass your code review?
Share the loop with a colleague without revealing the answer.