We are using db2 v 7.1 running @ OS 390. We are using the bellow query for retrieving a record from the database.
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.
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?