Oninit
Talk to an expert →

Informix Error -574: A subquery has returned not exactly one column.

Cause and resolution

A subquery has returned not exactly one column.

This statement contains a subquery (a SELECT statement within parentheses) to represent a single value (on one side of a relational operator such as equals). Such a subquery must return exactly one row and one column; otherwise, it is unclear which value the subquery represents. This subquery has returned more than one value. This error can occur if you specify the ITEM keyword in a collection subquery with more than one element in the projection list.

Review all subqueries to make sure that they can return at most one column (by listing only one expression after SELECT) and at most one row (by testing a unique key in the WHERE clause). For a collection subquery, either drop the ITEM keyword or make sure that the projection list contains only one element.

Oninit® Troubleshooting Guidance

Reasons / Common Causes

-574 fires when a subquery used to represent a single scalar value (on one side of a relational operator like =) returns more than one column — per the official guidance, such a subquery must return exactly one row and one column, or it's unclear which value it represents.

  1. A subquery's SELECT list naming more than one expression, when only a single value was needed on the other side of the comparison.
  2. SELECT * used inside a scalar subquery against a multi-column table, rather than naming the single column actually needed.
  3. The ITEM keyword used in a collection subquery whose projection list has more than one element, per the official guidance — ITEM specifically requires a single-element projection.
Solutions / Resolution
  1. List only one expression after SELECT in the subquery, per the official guidance, so it returns a single column.
  2. For a collection subquery using ITEM, either drop the ITEM keyword or reduce the projection list to a single element, per the official guidance — the two aren't compatible together.
  3. Also confirm the subquery returns at most one row (per the official guidance, by testing a unique key in the WHERE clause) — a column-count fix alone doesn't address a possible multi-row problem, which the server reports as a separate error from -574.
Examples
Too many columns in a scalar subquery
SELECT * FROM orders WHERE customer_id =
  (SELECT customer_id, region_id FROM customers WHERE email = 'a@example.com');
-- -574: the subquery returns two columns
Corrected — a single column
SELECT * FROM orders WHERE customer_id =
  (SELECT customer_id FROM customers WHERE email = 'a@example.com');
An ITEM collection subquery with too many projected elements
SELECT * FROM orders WHERE 5 IN ITEM(SELECT item_id, qty FROM order_items);
-- -574: ITEM requires a single-element projection list

SELECT * FROM orders WHERE 5 IN ITEM(SELECT item_id FROM order_items);
Diagnostic Checks
  1. Count the expressions listed after SELECT in the subquery and reduce to exactly one if it's being used as a scalar value.
  2. Check whether ITEM is used with a multi-element projection list, if the subquery is a collection subquery.
Related Errors / Related Topics

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

A scalar (or ITEM) subquery must return exactly one column — narrow the SELECT list to a single expression.