Informix Error -9059: A select statement which is not the final select statement in an ACE report must select into a temporary table.
Cause and resolution
A select statement which is not the final select statement in an ACE report must select into a temporary table.
The SELECT section of the report can contain more than one SELECT statement. However, only the last one of these statements can produce the rows that are the report contents. The preceding SELECT statements are used to prepare data in temporary tables that the final statement delivers to the report. Change the report specification so that all but the last SELECT statement contain INTO TEMP clauses.
Oninit® Troubleshooting Guidance
Reasons / Common Causes
-9059 fires when a non-final SELECT statement in the report's SELECT section doesn't select into a temporary table — per the official guidance.
- A non-final SELECT statement missing an INTO TEMP clause, per the official guidance — the direct, only cause; only the last SELECT statement may deliver rows directly to the report.
Solutions / Resolution
- Add an INTO TEMP clause to every SELECT statement except the last one, per the official guidance.
Examples
A non-final SELECT without INTO TEMP
SELECT * FROM orders; -- -9059: not the final SELECT, missing INTO TEMP
SELECT * FROM temp_orders;
Corrected
SELECT * FROM orders INTO TEMP temp_orders;
SELECT * FROM temp_orders;
Diagnostic Checks
- Check every SELECT statement in the SELECT section except the last one for an INTO TEMP clause, and add one where missing.
Related Errors / Related Topics
- -9061 — "A 'SELECT INTO' statement cannot be executed as the last select statement in an ACE report." The inverse constraint — the last SELECT must NOT use INTO TEMP.
- -9068 — "An SQL statement has been found in the SELECT section that is not a SELECT statement." A related SELECT-section validity error, on statement type rather than INTO TEMP usage.
A non-final SELECT statement in the report's SELECT section was missing its INTO TEMP clause — add one.