Results 1 to 7 of 7
  1. #1
    Join Date
    May 2011
    Posts
    3

    Unanswered: DB2 Conversion Error

    I am getting below error when I am type casting String to Int in where clause.

    Invalid Character found in a character string argument of the function "INT"
    SQLSTATE = 22018

    WHERE INT(R_FR_CD) <= 123456 AND INT(R_TO_CD) >= 12389

  2. #2
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    The error message fully explains the reason.

    For example:
    Code:
    ------------------------------ Commands Entered ------------------------------
    VALUES INT('');
    ------------------------------------------------------------------------------
    
    1          
    -----------
    SQL0420N  Invalid character found in a character string argument of the 
    function "INTEGER".  SQLSTATE=22018
    What are the values of R_FR_CD and R_TO_CD?

  3. #3
    Join Date
    May 2011
    Posts
    3
    The values are 123456 and 200007.
    I didn't get why VALUES is being used before INT(' ')

  4. #4
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    The values are 123456 and 200007.
    Really?

    This worked without any error.
    Code:
    ------------------------------ Commands Entered ------------------------------
    WITH
     test_data(R_FR_CD , R_TO_CD) AS (
    SELECT '123456' , '200007' FROM sysibm.sysdummy1
    )
    SELECT *
     FROM  test_data
     WHERE INT(R_FR_CD) <= 123456 AND INT(R_TO_CD) >= 12389
    ;
    ------------------------------------------------------------------------------
    
    R_FR_CD R_TO_CD
    ------- -------
    123456  200007 
    
      1 record(s) selected.
    I didn't get why VALUES is being used before INT(' ')
    VALUES INT('') is equivalent to SELECT INT('') FROM sysibm.sysdummy1

  5. #5
    Join Date
    May 2011
    Posts
    3
    The query which you posted worked even for me, but same applies to my query which fails

    May be some issue will get back to you soon...

    Thanks,
    Mahesh

  6. #6
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    ..., but same applies to my query which fails
    So, the values of R_FR_CD and R_TO_CD are not '123456' and '200007'.

  7. #7
    Join Date
    May 2009
    Posts
    508
    Provided Answers: 1
    dmaheshrao, You may have some stray non-printable characters in you data.
    Code:
    SELECT INT('123') FROM SYSIBM.SYSDUMMY1
    
    -----------
            123
    
    SELECT INT('123' || x'00') FROM SYSIBM.SYSDUMMY1
    
    -----------
    SQL0420N  Invalid character found in a character string argument of the function "INTEGER".  SQLSTATE=22018
    Try using the HEX function and see what shows up:
    Code:
    SELECT HEX('123' || x'00') FROM SYSIBM.SYSDUMMY1
    
    31323300

Posting Permissions

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