Oninit
Talk to an expert →

Informix Error -614: TEXT and BYTE columns are not allowed in the "order by" clause.

Cause and resolution

TEXT and BYTE columns are not allowed in the "order by" clause.

This SELECT statement selects one or more BYTE or TEXT values, and also specifies those columns in the ORDER BY clause. This action is not supported. Because no defined lexical order to BYTE or TEXT values exists, the database server cannot order them. (This is true even of substrings selected from a BYTE or TEXT column.) Review your SELECT statement to ensure that he correct columns are named in the ORDER BY clause.

Oninit® Troubleshooting Guidance

Reasons / Common Causes

-614 fires when a SELECT's ORDER BY clause names a TEXT/BYTE column — per the official guidance, these types have no defined lexical order, so the server can't sort rows by them, even when the value used is a substring of a TEXT/BYTE column rather than the whole thing.

  1. A TEXT/BYTE column named directly in ORDER BY — the direct, only cause.
  2. A substring of a TEXT/BYTE column named in ORDER BY, per the official guidance's explicit note — substring notation doesn't escape the restriction.
  3. A generic "order by the last column selected" query pattern, where the last selected column happened to be a BLOB column.
Solutions / Resolution
  1. Review the SELECT statement to confirm the correct columns are named in ORDER BY, per the official guidance — remove the TEXT/BYTE column (or substring of one) from the ordering clause.
  2. Order by a derived, comparable value instead, if ordering by some property of the BLOB content is genuinely needed — extract that property into a separate column at insert/update time (application-level, since Informix has no built-in conversion from TEXT/BYTE, per -608/-609), and order by that column instead.
Examples
The disallowed attempt
SELECT doc_id, content FROM documents ORDER BY content;
-- -614: content is TEXT
Ordering by a derived column instead
ALTER TABLE documents ADD content_length INT;
-- content_length populated at the application level
SELECT doc_id, content FROM documents ORDER BY content_length;
Diagnostic Checks
  1. Scan the ORDER BY clause for any TEXT/BYTE column or substring of one, and replace it with a comparable derived column.
Related Errors / Related Topics
  • -611 — "Scroll cursor can't select TEXT or BYTE columns." Same family of restrictions rooted in TEXT/BYTE's lack of a defined lexical ordering.
  • -612 — "TEXT and BYTE columns are not allowed in the group by clause." Same family.
  • -613 — "TEXT and BYTE columns are not allowed in the distinct clause." Same family.

Applies even to a substring of a TEXT/BYTE column, per the official guidance — order by a derived, comparable column instead.