Oninit
Talk to an expert →

Informix Error -249: Virtual column must have explicit name.

Cause and resolution

Virtual column must have explicit name.

When you select INTO TEMP, you are creating a table. As with any table, the columns of a temporary table must all have names. When you select a single column, the column in the temporary table receives the same name. When you select an expression, you must supply a name using a column alias, as in the following example:

SELECT order_num, ship_date, ship_date + 14 expected FROM orders INTO TEMP ord_dates

The temporary table ord_dates has three columns, which are named order_num, ship_date, and expected. The same principle applies to a view: each column must have a name. When you select every column of a view from a table, the view can have the same column names by default. When you derive any column of a view from an expression, you must give all the columns explicit names, as in the following example:

CREATE VIEW ord_dates(order_num, ship_date, expected) AS SELECT order_num, ship_date, ship_date + 14 FROM orders

Oninit® Troubleshooting Guidance

Reasons / Common Causes

-249 is a clear, well-documented naming requirement: every column of a temporary table or a view must have a name, and an expression (rather than a plain column reference) doesn't get one automatically.

  1. A SELECT INTO TEMP selecting an expression without an alias. A plain column keeps its original name in the resulting temp table; an expression like ship_date + 14 has no name of its own and needs one supplied.
  2. A CREATE VIEW deriving at least one column from an expression, without an explicit column-name list. The same underlying requirement as temp tables — a view can't have an unnamed column either.
  3. A generic query-building or ETL tool dynamically constructing SELECT INTO TEMP or CREATE VIEW statements with computed columns, not accounting for this naming requirement.
  4. A view-specific nuance worth being precise about: if any column in a view is derived from an expression, the CREATE VIEW column list must name all the view's columns — not just the expression-derived one. It's easy to assume only the computed column needs naming.
Solutions / Resolution
  1. For SELECT INTO TEMP, add a column alias to any expression in the select list.
  2. For CREATE VIEW, if any column is derived from an expression, supply an explicit column-name list naming every column of the view, not just the expression-derived one.
  3. For generic or dynamically-generated SQL, ensure computed columns are always explicitly aliased or named.
Examples
SELECT INTO TEMP with an alias
SELECT order_num, ship_date, ship_date + 14 expected
FROM orders INTO TEMP ord_dates;

The resulting ord_dates table has three named columns: order_num, ship_date, and expected — the alias on the third gives the computed expression its name.

CREATE VIEW with a full explicit column list
CREATE VIEW ord_dates(order_num, ship_date, expected) AS
  SELECT order_num, ship_date, ship_date + 14 FROM orders;

Note that all three column names are listed, even though order_num and ship_date are plain passthroughs — the presence of the computed expected column requires naming every column in the view, not just that one.

The common mistake: naming only the computed column
-- Wrong: assuming only the expression column needs a name
CREATE VIEW ord_dates(expected) AS
  SELECT order_num, ship_date, ship_date + 14 FROM orders;
-- -249: order_num and ship_date also need explicit names in this list
Diagnostic Checks
  1. Review the select list for expression columns lacking an alias, in either a SELECT INTO TEMP or a CREATE VIEW context.
  2. For views specifically, confirm whether an explicit column-name list is present and complete if any column is expression-derived — remember it must name every column, not just the computed one.
Related Errors / Related Topics
  • -201 — "A syntax error has occurred." The general SQL-parsing-error family this fits into.
  • -234 — "Cannot insert into virtual column column-name." Another view-related restriction concerning how computed/virtual columns behave differently from ordinary ones.

Remember the "all or nothing" rule for views: naming one computed column means naming every column in the CREATE VIEW list, not just that one.