Informix Error -1213

Reasons / Common Causes

Informix raises -1213 when a character value has to be interpreted as a number and cannot be. The conversion itself is rarely the interesting part — what matters when troubleshooting is where the conversion came from, because in most real cases nobody deliberately asked for one.

A valid numeric string may contain white space, digits, a sign, a decimal separator, and an exponent marker (e). Anything else — a letter, a currency symbol, a thousands separator, an embedded space, a stray control character — makes the string unconvertible.

The realistic causes fall into six groups:

1. An explicit CAST or conversion function against data that does not support it.
2. An implicit conversion Informix performs on your behalf, because a character value met a numeric column or expression. No CAST appears anywhere in the SQL.
3. Dirty data in a character column — a minority of rows carry N/A, -, unknown, an empty string, or free text, and the statement fails only when the engine reaches one of them.
4. Application or driver parameter binding — the SQL is correct, but a character value is bound to a numeric or MONEY placeholder at runtime.
5. Locale and numeric formatting — the value is a perfectly good number somewhere, but not under the active DBMONEY, DBFORMAT, or client locale settings.
6. Legacy application assignment, particularly Informix 4GL and ESQL/C, where a CHAR variable is assigned to a numeric variable.

A diagnostic point worth internalising: the absence of CAST() in the SQL proves nothing. Developers routinely search their statement for a conversion, find none, and conclude the error must be coming from somewhere else. Groups 2, 4, 5, and 6 all produce -1213 with no conversion visible in the text of the statement.

A second one: -1213 is frequently intermittent. If the bad value is in one row out of a million, the statement will succeed in development, succeed in testing, succeed for months in production, and fail the day that row is processed. "It worked yesterday" does not rule this out; it is the normal presentation.

A third, and the one that costs the most time: the failure can depend on the query plan rather than on the data. When a character column is compared to a numeric value, the conversion is applied wherever the optimiser chooses to evaluate that predicate — which may be against far more rows than the query's other filters would leave. The same statement can therefore fail written one way and succeed written another, over identical data. See The same query, three spellings below.

Solutions / Resolution

Work from the statement outward:

1. Capture the failing statement and its runtime values. Not the SQL as written in the source, but the SQL with the values actually bound. The value is the evidence; the statement usually is not.
2. Identify every character-to-numeric transition in it. For each comparison, assignment, insert, and function argument, ask which side is character and which is numeric.
3. If a character column is involved, profile it before changing anything (see the query below). Find out how many rows are unconvertible and what they look like — this usually identifies the cause immediately.
4. Decide whether the data or the schema is wrong. A column that has held N/A for years is not going to stop; either the data needs cleaning and a constraint, or the query needs to tolerate it.
5. Make the conversion explicit and guarded rather than leaving it implicit. Filter out non-numeric rows before converting, so the engine never reaches them.
6. Check locale and formatting settings if the value looks numeric to a human — 1.234,56, $199.95, 1,234 are all "obviously numbers" and all unconvertible under the wrong settings.
7. Fix binding at the application layer where that is the cause. Converting in the application is almost always better than making the database absorb text.
8. Re-test with the specific value that failed, not with a representative one.

Finding the offending rows

Profile the column before anything else. On a VARCHAR column being read as a number:

-- How bad is it, and what do the bad values look like?
SELECT value, COUNT(*) AS occurrences
FROM   example
WHERE  value IS NULL
   OR  TRIM(value) = ''
   OR  TRIM(value) NOT MATCHES '[-+0-9. ]*'
GROUP  BY value
ORDER  BY occurrences DESC;

MATCHES is a cheap first pass and will catch letters, symbols, and separators. It will not catch structurally invalid numbers such as 1.2.3 or --5, so treat a clean result as "no obvious junk" rather than "guaranteed convertible."

To find the row that actually broke a specific statement, narrow by range rather than converting everything:

SELECT ROWID, value
FROM   example
WHERE  TRIM(value) NOT MATCHES '[-+0-9. ]*'
   AND ROWID > 0
ORDER  BY ROWID;

Converting safely once you know what is in there

-- Convert only the rows that can be converted
SELECT CASE
         WHEN TRIM(value) MATCHES '[0-9]*'
         THEN CAST(value AS INTEGER)
         ELSE NULL
       END AS numeric_value
FROM   example;

Note that Informix evaluates the branches of a CASE before applying the filter in some plans; if the guarded CASE still raises -1213, push the filter into a subquery or a temporary table so the unconvertible rows are eliminated first:

SELECT CAST(value AS INTEGER) AS numeric_value
FROM   (SELECT value
        FROM   example
        WHERE  TRIM(value) MATCHES '[0-9]*'
          AND  TRIM(value) != '');

Examples

Explicit CAST failure

The simplest form, and the one everybody recognises:

CREATE TABLE example
(
    value VARCHAR(20)
);

INSERT INTO example VALUES ('1234');
INSERT INTO example VALUES ('ABC');

SELECT CAST(value AS INTEGER)
FROM   example;

1234 converts. ABC does not. The SELECT may return the first row successfully and fail when the engine reaches the second — which is why the error can appear partway through a result set or partway through a batch job.

Implicit conversion — no CAST in sight

CREATE TABLE orders
(
    order_no INTEGER
);

SELECT *
FROM   orders
WHERE  order_no = 'ABC123';

There is no CAST here, but Informix must compare an INTEGER column with a character literal. It converts the literal to the column's type, and the conversion fails.

This is the single most under-recognised cause of -1213. It appears constantly in generated SQL, in ORMs that quote every value, in search screens that pass a free-text box straight into a numeric column, and in reporting tools that build predicates as strings.

The same applies in joins:

-- customer_ref is VARCHAR, cust_id is INTEGER
SELECT *
FROM   orders   o
JOIN   customers c ON o.customer_ref = c.cust_id;

The join condition forces a conversion on every row examined. This one is also a performance problem — the conversion usually prevents index use on cust_id — so it is worth fixing even where the data happens to be clean.

The same query, three spellings — one of them fails

This case breaks the assumption that a -1213 is a fact about the data.

key_1 is a character column. This statement fails with -1213:

SELECT MAX(event_datetime), MAX(create_datetime)
FROM   activity_log
WHERE  ref_id = 4471003 AND
       func_id = "APPROVED" AND
       key_1 = 1;

Both of these succeed, against exactly the same data:

       key_1 = "1"           -- succeeds
       key_1::INT = 1        -- succeeds

Why the first one fails. key_1 is character and 1 is numeric, so Informix converts to compare — and it converts the column, not the literal. The conversion is therefore attempted on every key_1 value the predicate is evaluated against. Crucially, that is not necessarily the rows the other two predicates select. If the optimiser evaluates key_1 = 1 early — as an index filter, or as a scan filter applied before the ref_id and func_id restrictions have narrowed the set — then it converts key_1 on rows the query was never interested in. One piece of non-numeric data anywhere in that wider set raises -1213.

Why key_1 = "1" succeeds. Both sides are character. There is no conversion at all, so there is nothing to fail. This is the correct fix.

Why key_1::INT = 1 succeeds. The conversion here is explicit, so it might be expected to fail in the same way — but casting the column turns the predicate into an expression, which changes when and where it can be evaluated. In practice it can no longer be pushed down as an index or early scan filter, so it is applied later, to the small set of rows that already satisfy ref_id and func_id — and those rows happen to hold clean values.

That is the important consequence: the cast did not fix the data, it moved the conversion. Which means:

• The error can appear or disappear after UPDATE STATISTICS, after an index is added or dropped, after a version upgrade, or simply with a different literal value — with no change to the data at all.
• A cast that "fixes" the problem is relying on plan shape, and can regress the moment the plan changes.
• Copying the table to a test system may not reproduce it, because the statistics and therefore the plan differ.
• The rows containing the bad data may be completely unrelated to the rows the query returns, so profiling only the result set will find nothing.

Compare the plans to confirm this is what is happening:

SET EXPLAIN ON;

Then run both spellings and look at where the key_1 filter appears in each.

What to do. Match the types — compare a character column with a character literal. That removes the conversion entirely and, unlike the cast, leaves any index on key_1 usable:

       key_1 = '1'

Note the quoting. Double quotes work as string delimiters here only because DELIMIDENT is not set in this environment; where it is set, "1" is a delimited identifier and the statement fails differently (see error -201). Single quotes are unambiguous in both cases, so prefer them.

Longer term, decide what key_1 actually is. If it holds numbers, it should be a numeric column. If it is a generic key column holding different kinds of value depending on func_id — which is the usual reason a column like this exists — then it is character by design, every caller must compare it as character, and that is worth stating in the schema documentation rather than rediscovering through a -1213.

Dirty data in a character column

Values held in a VARCHAR column that is treated as numeric downstream:

100
250
350
N/A
475

The conversion works for four rows out of five. A report that has run nightly for two years fails the morning after somebody typed N/A into a form.

Real-world unconvertible values, in rough order of how often they turn up:

Value	Why it fails
N/A, NA, -, ?, TBC	Placeholder text for "no value"
`` (empty string)	Not the same as NULL, and not a number
1,234	Thousands separator
$199.95, £50, €1200	Currency symbol
12 345	Embedded space as a separator
(500)	Accounting notation for negative
123X, 45kg, 10%	Unit or suffix carried along with the number
1.234,56	Decimal comma under a dot-decimal locale
12/05/2024	A date landing in a numeric field
1234\r	Carriage return from a Windows-sourced load file

That last one deserves attention because it is invisible. A value loaded from a CRLF file looks correct in every tool that displays it and still will not convert:

SELECT value, LENGTH(value), LENGTH(TRIM(value))
FROM   example
WHERE  LENGTH(value) != LENGTH(TRIM(value));

INSERT into a numeric column

CREATE TABLE invoice
(
    amount DECIMAL(10,2)
);

This is valid — Informix converts the character representation to DECIMAL:

INSERT INTO invoice VALUES ('125.50');

This is not:

INSERT INTO invoice VALUES ('125 dollars');

In a bulk load this presentation is characteristic: thousands of rows insert, then one fails and takes the transaction with it. dbload with a suitable error limit, or loading into an all-character staging table and profiling before the real insert, avoids losing the run.

Application and driver parameter binding

The statement is correct:

UPDATE sales
SET    price = ?
WHERE  sale_id = ?;

The failure is in what gets bound. A form field carrying $199.95 is passed through as a string to a MONEY or DECIMAL column:

// JDBC — the currency symbol arrives intact from the input field
pstmt.setString(1, request.getParameter("price"));   // "$199.95"
pstmt.setInt(2, saleId);
pstmt.executeUpdate();                               // -1213

# Python DB-API — same shape, same result
cur.execute("UPDATE sales SET price = ? WHERE sale_id = ?",
            ("$199.95", sale_id))

The fix belongs in the application: strip and validate the input, and bind it with the correct type (setBigDecimal, a Decimal, a double) rather than as a string. Binding numerics as strings works right up until a value arrives that is not clean, which makes it a latent fault rather than a working pattern.

The same applies to ODBC and ESQL/C host variables where a char host variable is used against a numeric column.

Locale and decimal separators

These two values are not interchangeable in every environment:

1234,56
1234.56

Under a locale where the comma is the decimal separator, the first converts and the second may not — and on a differently configured client, the reverse. The value is a valid number to a human reading it, which makes this class of failure disproportionately time-consuming.

Check what the session is actually working with:

echo "$DBMONEY"
echo "$DBFORMAT"
echo "$CLIENT_LOCALE"
echo "$DB_LOCALE"
echo "$GL_DATE"

The classic presentation: an application works from one office and fails from another, or works from the application server and fails when run by hand, because the two environments export different DBMONEY or CLIENT_LOCALE values. Compare the environment of the failing process against a working one before looking at the data at all.

DBMONEY also governs leading currency symbols. If DBMONEY is set to $. then $199.95 is convertible to MONEY; if it is not set that way, the same string is not. This is why a MONEY column sometimes accepts a currency-prefixed string and sometimes does not.

Informix 4GL

Legacy code is a substantial source of -1213 and should not be skipped over. In 4GL the conversion happens on assignment, with nothing resembling SQL involved:

DEFINE input_value   CHAR(20)
DEFINE numeric_value INTEGER

LET input_value   = "123X"
LET numeric_value = input_value

The LET requires a character-to-integer conversion and fails with -1213.

The usual source is a screen field. A CHAR field on a form accepts whatever the operator types, and the failure surfaces later at the assignment or at the INSERT:

INPUT BY NAME p_qty_char
LET p_qty = p_qty_char        -- -1213 when the operator typed "10 ea"

Defining the form field as INTEGER or DECIMAL pushes validation to the point of entry, where it belongs. Where changing the form is not practical, validate before assigning.

ESQL/C has the equivalent problem with host variables, and the same remedy — use a matching host variable type, or call the conversion functions (rstoi, rstol, deccvasc) and check their return codes rather than assigning and hoping.

Diagnostic Checks

A repeatable sequence for a -1213 that is not immediately obvious:

1. Get the real statement with real values. Turn on the application's SQL logging, or use onstat -g his / SQL tracing to capture what was actually executed.

   EXECUTE FUNCTION task('set sql tracing on', 1000, 1024, 'low', 'user', 'username');
   

   Then read it back from syssqltrace:

   SELECT sql_id, sql_statement, sql_runtime
   FROM   sysmaster:syssqltrace
   WHERE  sql_statement LIKE '%sales%'
   ORDER  BY sql_id DESC;
   

2. Compare the column types on both sides of every comparison, join, and assignment:

   SELECT c.colname, c.coltype, c.collength
   FROM   syscolumns c
   JOIN   systables  t ON c.tabid = t.tabid
   WHERE  t.tabname = 'orders'
   ORDER  BY c.colno;
   

3. Profile any character column feeding the conversion, using the query in the Solutions section.
4. Compare the failing environment to a working one — DBMONEY, DBFORMAT, CLIENT_LOCALE, DB_LOCALE, GL_DATE.
5. Reduce to the smallest reproducer. A single SELECT against a single row. If it will not reproduce with the specific value, the value is not the cause and the binding layer is.

Related Errors / Related Topics

• -1214 — A character to decimal conversion process failed. The same family, specific to DECIMAL.
• -1215 — A character to integer conversion process failed.
• -1226 — Decimal or money value exceeds maximum precision. The value converted but does not fit — reached after -1213 is resolved, and often the next error in a data-cleaning exercise.
• -1260 / -1262 / -1263 — Further conversion failures between specific types, worth checking if the value is not obviously character data.

If -1213 is appearing in volume rather than as a one-off, it is usually a schema or interface design issue — numeric data held in character columns, or an application layer binding everything as strings — rather than a data-quality accident, and is best addressed at that level.