Oninit
Talk to an expert →

Informix Error -9632: Value does not match the type of column (column_name).

Cause and resolution

Value does not match the type of column (column_name).

If the value does not match the data type of the column, but can be converted into the data type of the column, then you might need to use an implicit or explicit cast.

If you want the conversion to be automatic, then create an implicit cast by using the CREATE IMPLICIT CAST statement to define a cast between the source and destination types. See the description of the CREATE CAST statement in the IBM Informix Guide to SQL: Syntax.

In some cases, you might need to use explicit casting. A common cause of error -9632 is failure to explicitly cast a row type. For example, the following INSERT statement would cause error -9632:

CREATE ROW TYPE rt1 (i INT, f FLOAT); CREATE TABLE table1 (rt1_col rt1); INSERT INTO table1 (rt1_col) VALUES (ROW(1, 2.0));

The correct INSERT statement, with the explicit cast, follows:

INSERT INTO table1 (rt1_col) VALUES (ROW(1, 2.0)::rt1);

Oninit® Troubleshooting Guidance

Reasons / Common Causes

-9632 fires when a value's data type doesn't match the named column's, with no automatic conversion available — per the official guidance.

  1. A convertible value with no implicit cast defined between its type and the column's type, per the official guidance — the first named cause.
  2. A row-type value that needs an explicit cast but wasn't given one, per the official guidance — the second named cause; a common cause of this error.
Solutions / Resolution
  1. Use an implicit or explicit cast if the value can be converted to the column's data type, per the official guidance.
  2. Create an implicit cast with CREATE IMPLICIT CAST between the source and destination types, per the official guidance, if automatic conversion is wanted.
  3. Explicitly cast the value, per the official guidance — commonly needed for row-type values.
Examples
A row-type value needing an explicit cast
INSERT INTO tab VALUES (ROW(1, 'a')::my_row_type);
Diagnostic Checks
  1. Check the value's type against the column's declared type, and add an explicit cast or define an implicit cast with CREATE IMPLICIT CAST.
Related Errors / Related Topics
  • -9633 — "ALTER TABLE cannot modify column (column_name) type. Need a cast from the current type to the new type." A related casting error, for ALTER TABLE column-type changes rather than value assignment.
  • -9634 — "No cast from ." A related casting error, on the cast/cast function not existing at all rather than simply not being applied.

A value's type didn't match the column's type and no cast was applied — add an explicit cast, or define one with CREATE IMPLICIT CAST.