Re: Embedded SQL Error 22023

DLee@xxxxxxxx wrote:

Could use some help

When running an embedded sql program I get SQLSTATE error 22023
SQL CODE -802 which means a parameter or host variable value is
invalid. And it's true, I discovered on some records, where the
entry time and date fields have blanks, like hex 404040.

These don't seem to be causing any problems anywhere in the
system (yet), and I really don't want to get into the process of
correcting them if I don't have too. Is there a way I can run
this SQL, and ignore those two fields?

They are defined as numeric 6,0 for time and 8,0 for date.

The SQL0802 is not specific to host variable values. If a host variable is not named, i.e. when "the host variable name is *N", then the error may have occurred for example "when attempting to resolve a search condition." I would expect an error with "a parameter or host variable value" only with SQLCODE of -302, -304, or -406. Given the SQL states are common but the decimal data error for field [vs column] data is somewhat unique to DB2 for i, the docs for that SQLSTATE may be lacking, for when paired with -802 on i. Consider for example, a SQL query that has an OR condition in the WHERE clause could run [potentially even for years] under one implementation\optimization whereby the logic just happened to resolve true on the determinable test(s), then only start failing when the optimization\implementation of the query [its "access plan"] attempted to or was required to test the other condition which has an indeterminate\bad value; e.g. a CPF5035 rc1 for EBCDIC blanks in a numeric field. AFaIK, except in the CASE, a mapping error trumps the logic such that the row selection or omission fails; i.e. mapping_error|untested=error whereas true|untested=true.

For lack of each of... the statement describing "this SQL", the definitions of the file(s), and the name of the two numeric fields with the bad decimal data, any attempt to describe how to "ignore those two fields" is really quite difficult. No matter, the only truly legitimate fix is to ensure the bad data does not exist [i.e. correct the bad data already in the file row(s)] and that no new bad data is placed in the rows by either insert or update activity. Although the latter is accomplished easiest by replacing the database file with an SQL TABLE, whatever program(s) which may be responsible for inserting\updating the bad data will then start to fail [instead of what I presume is an inquiry failing in the given scenario].

Regards, Chuck