Query fails with SQL0420 error

Answer

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.

Example:

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'),
                               ('abcdefgh')"
db2 "CREATE VIEW vw1(col1,col2) AS
       SELECT CAST(substr(col1,4) AS INTEGER),
              SUBSTR(col1,1,2)
       FROM basetab"


Case 1:

Consider the following query:

db2 "SELECT * FROM vw1 WHERE col1 <>0 AND col2 = '12'"

COL1            COL2
-----------     ---------
     456789     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.


Case 2:

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 "

COL1        COL2
----------- ----
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.


Workaround

Do not store non-numeric values in columns that will be cast to integer in queries.