MainframeKit

-420

SQLSTATE 22018NEGATIVE SQLCODE

A character string handed to a function or cast is not in a form that function accepts, such as letters or spaces passed where a number is expected.

MOST LIKELY

A CHAR column holding spaces, letters or punctuation, cast to a numeric type.

FIRST CHECK

Note the function or data type named in the message.

TRY IT ▸

Record decoder

What it means

Some built-in functions and casts need their string argument in a particular shape. DECIMAL or INTEGER, for example, need the string to look like a valid numeric constant. When it does not, Db2 stops the statement with -420 and SQLSTATE 22018. The message names the function; for a cast it names the target data type instead.

This can appear even when you wrote no function. If you compare a character column with a number, Db2 converts the string to DECFLOAT(34) behind the scenes, and a non-numeric value in that column then fails with -420 naming DECFLOAT.

Typical causes

  • A CHAR column holding spaces, letters or punctuation, cast to a numeric type.
  • Comparing a character column to a numeric literal or host variable, so every row is converted.
  • Bad data loaded into a column that is normally numeric text.
  • A host variable not initialised, so it contains blanks or low values.

How to fix it

  1. Note the function or data type named in the message.
  2. Find the rows whose value is not valid for that function, for example with TRANSLATE or a simple pattern check.
  3. Compare the character column to a character literal instead of a number, so no conversion is needed.
  4. Correct or clean the bad data with the data owner.
  5. Initialise host variables before the statement runs.

Example

Illustrative only; names and values are invented.

SELECT ... FROM ACCTTAB WHERE ACCT_CODE = 1050
-- ACCT_CODE is CHAR(6) and one row holds 'N/A   '
-- SQLCODE -420, function DECFLOAT, SQLSTATE 22018