Informix Error -659

-659 INTO TEMP table required for SELECT statement.

A SELECT statement did not specify where to put the returned values. SELECT statements within a procedure require either an INTO TEMP clause or an INTO clause that references the appropriate procedural variables.

Example of error:

CREATE PROCEDURE testproc() ... SELECT col1, col2 FROM tab; -- error END PROCEDURE

Correction:

CREATE PROCEDURE testproc() ... SELECT col1, col2 INTO var1, var2 FROM tab; SELECT col1, col2 FROM tab INTO TEMP another_table; END PROCEDURE

Oninit® Troubleshooting Guidance

Reasons / Common Causes

-659 fires when a SELECT inside an SPL routine doesn't specify where its returned values go — per the official guidance, a SELECT within a routine needs either an INTO TEMP clause (to stage results into a temp table) or an INTO clause naming procedural variables to receive the values directly.

A SELECT copied from ordinary client-side SQL into a routine body, where an interactive or client-fetched SELECT doesn't need an INTO clause but one inside a routine does.
A SELECT intended as part of a FOREACH but written as a standalone statement instead, losing the implicit row-by-row handling FOREACH provides.
A SELECT expected to return exactly one row, missing the INTO clause naming the variables that single row's columns should populate.

Solutions / Resolution

Add an INTO clause naming procedural variables, if the SELECT returns exactly one row:
```
SELECT status INTO v_status FROM orders WHERE order_id = p_order_id;
```
Or add INTO TEMP tempname, if the SELECT may return multiple rows and the results need to be staged for further processing:
```
SELECT * FROM orders WHERE customer_id = p_customer_id INTO TEMP recent_orders;
```
Or use FOREACH instead, if the intent was to process multiple rows one at a time rather than stage them into a temp table:
```
FOREACH SELECT order_id, status INTO v_id, v_status FROM orders
  ...
END FOREACH;
```

Examples

The disallowed attempt

CREATE PROCEDURE check_order(p_order_id INT)
  SELECT status FROM orders WHERE order_id = p_order_id;
  -- -659: no INTO clause naming where the result goes
END PROCEDURE;

Corrected — INTO a variable

CREATE PROCEDURE check_order(p_order_id INT)
  RETURNING CHAR(10);
  DEFINE v_status CHAR(10);
  SELECT status INTO v_status FROM orders WHERE order_id = p_order_id;
  RETURN v_status;
END PROCEDURE;

Diagnostic Checks

Scan every SELECT inside a routine body for a missing INTO/INTO TEMP clause, and add one, or convert the statement to FOREACH if multi-row iteration was the actual intent.

Related Errors / Related Topics

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

Every SELECT inside an SPL routine needs an explicit destination — INTO variables for one row, INTO TEMP for staging multiple, or FOREACH for row-by-row iteration.