IBM Support

SQL0401N error comparing data types of different values

Troubleshooting


Problem

You run a select statement that compares a string and an integer value. This results in an SQL0401N error on DB2 versions prior to v9.7. The same select statement runs fine on v9.7. db2 => select 1 from sysibm.sysdummy1 where 1='1' SQL0401N The data types of the operands for the operation "=" are not compatible. SQLSTATE=42818

Cause


Starting in v9.7 DB2 performs implicit casting.

http://publib.boulder.ibm.com/infocenter/db2luw/v9r7/index.jsp?topic=/com.ibm.db2.luw.wn.doc/doc/c0053519.html

This is why the comparison between the integer and string successfully works in v9.7.

In versions previous to v9.7, you must perform a manual CAST to compare data types of different values.

http://publib.boulder.ibm.com/infocenter/db2luw/v9r5/index.jsp?topic=/com.ibm.db2.luw.sql.ref.doc/doc/r0023459.html

Resolving The Problem


Use the CAST function to successfully compare your differing data types in versions previous to v9.7:

db2 => select 1 from sysibm.sysdummy1 where 1=CAST ('1' as integer)

1
-----------
1

1 record(s) selected.

[{"Product":{"code":"SSEPGG","label":"Db2 for Linux, UNIX and Windows"},"Business Unit":{"code":"BU058","label":"IBM Infrastructure w\/TPS"},"Component":"OTHER - Uncategorised","Platform":[{"code":"PF002","label":"AIX"},{"code":"PF010","label":"HP-UX"},{"code":"PF016","label":"Linux"},{"code":"PF027","label":"Solaris"},{"code":"PF033","label":"Windows"}],"Version":"9.7;9.5;9.1","Edition":"","Line of Business":{"code":"LOB10","label":"Data and AI"}}]

Document Information

Modified date:
16 June 2018

UID

swg21443029

Page Feedback