Oninit
Talk to an expert →

Informix Error -374

-374 Can only use column number in ORDER BY clause with UNION.

This query has both a UNION clause and an ORDER BY clause. In a union query, in which multiple SELECT statements exist and the names of the selected columns in each statement are not necessarily the same, you cannot use column names or expressions in the ORDER BY clause.

Instead, you must use column position numbers, with 1 representing the first selected column, 2 representing the second, and so on. Rewrite the query to use only numbers in the ORDER BY clause.

Oninit® Troubleshooting Guidance

Reasons / Common Causes

-374 is a UNION-specific ORDER BY restriction: once multiple SELECT statements are combined with UNION, the combined result no longer has a single, unambiguous set of column names or expressions to sort by — since each branch could in principle use different column names or expressions for the same output position — so ORDER BY must use positional column numbers instead.

  1. An ORDER BY clause using a column name or expression on a UNION query — the direct cause.
  2. A query originally written as a single SELECT (where ORDER BY column_name worked fine), later extended into a UNION, without updating the ORDER BY clause to use position numbers.
  3. Copy-pasted ORDER BY syntax from a non-UNION query applied to a UNION query without adjustment.
Solutions / Resolution
  1. Rewrite the ORDER BY clause to use column position numbers (1 for the first column, 2 for the second, and so on), per the official guidance, instead of column names or expressions.
  2. When adding a UNION to a previously single-SELECT query, review and convert its ORDER BY clause to positional form as part of the same change.
Examples
Column name in ORDER BY on a UNION query
SELECT customer_id, name FROM current_customers
UNION
SELECT customer_id, name FROM archived_customers
ORDER BY name;
-- -374: name isn't allowed here; use a position number

Fix — use the column's position instead:

SELECT customer_id, name FROM current_customers
UNION
SELECT customer_id, name FROM archived_customers
ORDER BY 2;
Diagnostic Checks
  1. Check whether the query uses UNION (or INTERSECT/MINUS) — this restriction is specific to combined queries.
  2. Check the ORDER BY clause for column names or expressions rather than plain position numbers.
Related Errors / Related Topics
  • -309 — "ORDER BY column or expression must be in SELECT list." A related ORDER BY restriction, in this case for DISTINCT/UNIQUE queries rather than UNION.
  • -308 — "The statement failed because corresponding column data types must be compatible for each UNION, INTERSECT, or MINUS query." Another UNION-specific restriction in the same general combined-query family.

ORDER BY on a UNION query must always use column position numbers — column names and expressions aren't accepted there, even if they'd work fine in a plain single-SELECT query.