Query fails with SQL0420 error
This document provides troubleshooting information for when your WHERE clause predicate order is reversed and you encounter an SQL0420N error.
The full error message is as follows:
SQL0420N Invalid character found in a character string argument of the function function-name.
Error SQL0420N is a runtime error that is raised when an error occurs during query processing that might not have occurred had the ordering of the query WHERE clauses been different. This error can occur during the runtime data retrieval process for a query with multiple predicates when an unsupported attempt is made to cast column data to integer type data.
If the column contains some data that cannot be successfully cast to integer (such as the value 'abcdefgh') and--because of the order of the WHERE clauses--the runtime data retrieval proceeds in such a way that it never retrieves this row, error SQL0420N would not be raised. This is shown in Case 1 in the example below.
However a reversal of the WHERE clause predicates within the same query could result in a different runtime data retrieval process in which the row containing the un-castable data value could be retrieved and the unsupported data type casting attempted prior to the row being filtered out by the secondary WHERE clause. DB2® Universal Database™ (DB2 UDB) identifies that the cause of the failure might have been avoided had the WHERE clause predicates been ordered differently, and so it returns error SQL0420N. This is shown in Case 2 of the example below.
To create the scenario required to demonstrate this scenario, a database, table, and data must be created as follows:
db2 "CREATE DATABASE testdb"
db2 "CONNECT TO testdb"
db2 "CREATE TABLE basetab(col1 VARCHAR(30))"
db2 "INSERT INTO basetab VALUES('123456789'),
db2 "CREATE VIEW vw1(col1,col2) AS
SELECT CAST(substr(col1,4) AS INTEGER),
Consider the following query:
db2 "SELECT * FROM vw1 WHERE col1 <>0 AND col2 = '12'"
1 record(s) selected.
In this case, at the second row of data, the predicate col2 = '12' happens to be evaluated first and since 'ab'='12' resolves to false and the row does not qualify for further processing, the predicate col1 <> 0 has never been evaluated hence the SQL0420N error is not reported.
When the WHERE clause predicate order is reversed, and the query is executed, it fails with error SQL0420N.
db2 "SELECT * FROM vw1 WHERE col2 = '12' AND col1 <>0 "
SQL0420N Invalid character found in a character string argument of the function "INTEGER". SQLSTATE=22018
In this case, at the second row of data, the predicate col1 <> 0 happens to be evaluated first and the run-time error SQL0420N is reported when DB2 tries to cast the non-numeric string to an integer value.
Do not store non-numeric values in columns that will be cast to integer in queries.
More support for:
DB2 for Linux, UNIX and Windows
Compiler - Optimizer
Software version: 7, 8
Operating system(s): AIX, HP-UX, Linux, Solaris, Windows
Software edition: Enterprise
Reference #: 1226768
Modified date: 30 December 2005