Results 1 to 4 of 4
  1. #1
    Join Date
    Mar 2003
    Posts
    69

    Unanswered: DB2 - SQL Select Query Issue

    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.

  2. #2
    Join Date
    Aug 2004
    Posts
    330
    Have you scrubbed the data for non-numeric characters (including spaces)?

  3. #3
    Join Date
    Mar 2003
    Posts
    69
    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?

  4. #4
    Join Date
    Mar 2003
    Posts
    69
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •