Informix Error -686

Reasons / Common Causes

-686 fires when a routine using RETURN ... WITH RESUME (which can produce multiple rows of output, iterator-style) is called from a context expecting only a single row of values — per the official guidance's example, a routine producing multiple rows called by another routine that expects a single return.

1. A routine written with RETURN ... WITH RESUME (making it a row-producing iterator routine) called somewhere that only handles one row of results, per the official guidance's example.
2. A routine intended as a single-row function, but mistakenly using WITH RESUME instead of a plain RETURN, causing it to behave as a multi-row iterator unintentionally.
3. A routine genuinely meant to produce multiple rows, called via a mechanism (like LET or a scalar expression) that only accepts one row, instead of via FOREACH or a table context that can consume multiple rows.

Solutions / Resolution

1. If a single value was intended, use a plain RETURN instead of RETURN ... WITH RESUME in the routine's own definition — this is likely the actual bug if only one row was ever meant to come back.
2. If multiple rows are genuinely intended, call the routine from a context that can consume them, such as FOREACH:

   FOREACH EXECUTE FUNCTION testroutine() INTO v_val
     ...
   END FOREACH;
   

   rather than a single-value LET/expression context.
3. Review why the routine has WITH RESUME at all if it was copy-pasted from an iterator-style routine template without needing that behavior.

Examples

The disallowed mismatch

CREATE FUNCTION testroutine()
  RETURNING INT;
  FOR i = 1 TO 3
    RETURN i WITH RESUME;
  END FOR;
END FUNCTION;

CREATE FUNCTION gettest()
  RETURNING INT;
  DEFINE v_val INT;
  LET v_val = testroutine();
  -- -686: testroutine returns multiple rows, this context expects one
  RETURN v_val;
END FUNCTION;

Corrected — consume multiple rows via FOREACH

CREATE FUNCTION gettest()
  RETURNING INT;
  DEFINE v_val, v_last INT;
  FOREACH EXECUTE FUNCTION testroutine() INTO v_val
    LET v_last = v_val;
  END FOREACH;
  RETURN v_last;
END FUNCTION;

Diagnostic Checks

1. Check whether the called routine uses RETURN ... WITH RESUME, and whether the caller is set up to consume multiple rows (FOREACH) or only a single value (LET/expression).

Related Errors / Related Topics

• -684 — "Function function-name returns too many values." A related return-mismatch error, about too many values in one row rather than too many rows.
• -685 — "Function function-name returns too few values." A related return-mismatch error, about too few values in one row.

RETURN ... WITH RESUME makes a routine multi-row — call it via FOREACH (or a similar multi-row-capable context) rather than a single-value assignment.