Hi,
We are using db2 v 7.1 running @ OS 390. We are using the bellow query for retrieving a record from the database.


Query
--------

select a.first_name, a.last_name from dbunt.address a, dbunt.addr_xref b where a.address_i = b.address_i and int(postal_c) = b.zip_code

Where the postal_c is a character of (10) and the zip_code is a integer value. Some time this query works fine and fetching the exact results some times it's giving the error , and the error is like,
""SQL0420N Invalid character found in a character string argument of the
function "INT". SQLSTATE=22018 ""

My question is, From the above error it looks like I can not use the int function for validation of a char to a integer. But why it's success at some times ( Believe a integer comparision is going through fine). If I want to re-modify the query, What's the best way I can achieve.
Any Suggestions/ Thoughts.

Thanks for the help!

Anto.

Have you scrubbed the data for non-numeric characters (including spaces)?

Our application is mainly made for US based projects. Since @ US it's always integer the address_xref table ( Which was created intially) has the integer for the zip Code. Now we have made some enhancements to support this new table can accept char's for zip code. Ofcourse , the scenario I mentioned, we don't want any char validation ( We require validation only for the US postal Code. ) We don't have any flag associated to identify whether the country is US or other country. So how could we achieve the validation?

Believe I solved the problem. I have the patch1 & patch2 missing in my db2cli.ini file.
So I said, PATCH2=18 & PATCH1=131072 and it's working now.