SQLCODE -401: Operands not comparable

What it means

Db2 can compare numbers with numbers and strings with strings. It can compare dates, times and timestamps with values of the same type, or with strings that hold a valid date or time. Mixing other types fails with -401 when the statement is bound or prepared, before any rows are read.

Db2 10 for z/OS changed this for strings and numbers. From Db2 10 new-function mode, comparing a character string with a number makes Db2 cast the string to DECFLOAT(34), so WHERE CHAR_COL = 123 no longer fails with -401. If a string holds something that isn't a number, you get -420 at run time instead. Programs that hit -401 for this reason on older releases may behave differently today.

The Db2 for z/OS message doesn't name the operator or the column. Db2 for LUW's SQL0401N does name the operator, which helps when the same SQL runs on both.

Common causes

• A date, time or timestamp column is compared with a number, for example WHERE ORDER_DATE = 20261018 or with a numeric host variable holding YYYYMMDD.
• A column with a distinct (user-defined) type is compared with a value of its source type without a cast. Distinct types are strongly typed.
• A binary string (BINARY or VARBINARY) is compared with a number, or in many cases with a character string.
• On releases before Db2 10, or before new-function mode, a character column was compared with a number.
• A comparison inside a CASE WHEN, an IN list or a BETWEEN mixes types, so one pair of operands doesn't match even though the others do.
• A column defined with a field procedure is compared with, or used in arithmetic with, a column that doesn't use the same field procedure.

How to find the problem

1. List every comparison and arithmetic operation in the statement, including those in ON clauses, subqueries and CASE expressions.
2. Look up each column's type in SYSIBM.SYSCOLUMNS (COLTYPE, LENGTH, SCALE) and each host variable's declaration in the program.
3. For distinct types, check SYSIBM.SYSDATATYPES for the source type.
4. If the statement reads a view and the SQL itself looks right, check the data types in the view definition.
5. If the statement is long, remove predicates one at a time in SPUFI or another query tool until the error stops.

How to fix it

• Compare like with like. For dates, use a date string such as '2026-10-18' or a function such as DATE(...).
• Convert explicitly with CAST, CHAR, DECIMAL, DATE or TIMESTAMP_FORMAT, so the result doesn't depend on implicit rules.
• Declare host variables with the same type as the column. DCLGEN output is the safest source.
• Cast distinct types with the cast function Db2 created for them, or cast the other side to the distinct type.

Technical reviewer: Albert Joseph, WND Software.

Last reviewed 2026-10-18 by the WND Software team. Written in our own words. For the full reference, check IBM’s Db2 for z/OS documentation for your release.