Informix Error -284: A subquery has returned not exactly one row.

Reasons / Common Causes

-284 is one of the most common everyday query-construction errors: a subquery used in place of a single literal value in an expression must return exactly one row and one column, and this one returned more than one.

1. A scalar subquery whose WHERE clause doesn't uniquely constrain the result, returning multiple rows when the surrounding expression expected exactly one.
2. Using a singleton SELECT (a plain SELECT ... INTO expecting one row) against a query that can actually return multiple rows — the proper approach for a genuinely multi-row result is DECLARE/OPEN/FETCH (a cursor), not a singleton select.
3. A WHERE clause condition that isn't actually unique, when the code assumes it uniquely identifies one row — filtering on a column that isn't backed by a unique constraint or index.
4. Data growth breaking a previously-safe assumption — a subquery or singleton select that worked when only one row ever matched historically, failing once a second matching row appears (an email column assumed unique but never actually constrained that way, for example — see -100 for the broader uniqueness-assumption theme).

Solutions / Resolution

1. Ensure the subquery returns a single row by using a WHERE clause backed by a unique index equality test, per the official guidance.
2. Alternatively, select only an aggregate function (MAX, MIN, COUNT, and similar) if a single summary value is genuinely all that's needed — aggregates always collapse to one row.
3. If genuinely expecting multiple rows, use DECLARE/OPEN/FETCH (a cursor) instead of a singleton SELECT.
4. Review whether the assumed uniqueness actually holds, and enforce it with a real unique constraint or index if it should — rather than relying on an assumption that happens to be true today.

Examples

A subquery without a unique constraint

SELECT name, (SELECT total FROM orders WHERE customer_id = c.id) AS last_order
FROM customer c;
-- -284: a customer with more than one order returns multiple
-- rows from the subquery

Fix — use an aggregate if a single summary value is what's needed:

SELECT name, (SELECT MAX(total) FROM orders WHERE customer_id = c.id) AS max_order
FROM customer c;

Or constrain to a genuinely unique row if that's the actual intent:

SELECT name, (SELECT total FROM orders WHERE order_id = c.latest_order_id) AS last_order
FROM customer c;

Singleton SELECT misuse

SELECT * INTO :rec FROM orders WHERE status = 'pending';
-- -284: more than one pending order exists

Fix — use a cursor if multiple rows are genuinely expected:

DECLARE cur1 CURSOR FOR SELECT * FROM orders WHERE status = 'pending';
OPEN cur1;
FETCH cur1 INTO :rec;

Diagnostic Checks

1. Test the subquery or singleton select independently to see how many rows it actually returns for the data in question.
2. Review the WHERE clause for whether it's genuinely backed by a unique index or constraint, rather than an assumption.

Related Errors / Related Topics

• -201 — "A syntax error has occurred." The general SQL-parsing-error family this fits into.
• -100 — "ISAM error: duplicate value for a record with unique key." Relevant to the underlying uniqueness-assumption theme — if a column was assumed unique but never actually constrained that way, this is the related enforcement mechanism worth adding.

Test the subquery independently first — it immediately reveals whether the "single row" assumption actually holds for the current data.