Results 1 to 5 of 5
  1. #1
    Join Date
    Apr 2010
    Posts
    5

    Unanswered: SQL0420N Invalid character found in a character string argument of the function INT

    I run query REFRESH TABLE DEV.MQT_AAA_APN_D_H7 on the sql tools such as toad is running well but i get errors when refresh table on db2 console.

    This query MQ tables (MQT_AAA_APN_D_H7):
    select DAY_ID, PRODUCT_ID, HIT, sum(sendkbytes) as SENDKBYTES, sum(receivekbytes) as RECEIVEKBYTES
    from DEV.aaa_apn_d_t
    WHERE day_id > cast(replace(char(current_date - 7 days),'-','') as integer) group by DAY_ID,PRODUCT_ID;

    is there any suggestion to replace the cast integer?


    bash-3.2$ db2 connect to mydb user myuaix using P@ssW0rd

    Database Connection Information

    Database server = DB2/AIX64 9.5.1
    SQL authorization ID = MYUAIX
    Local database alias = MYDB

    bash-3.2$ db2 "REFRESH TABLE DEV.MQT_AAA_APN_D_H7"
    DB21034E The command was processed as an SQL statement because it was not a
    valid Command Line Processor command. During SQL processing it returned:
    SQL0420N Invalid character found in a character string argument of the
    function "INTEGER". SQLSTATE=22018


    thanks for the response...

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    Your problem is the following expression: char(current_date - 7 days)

    It will be interpreted differently on different environments.

    On my system:
    Code:
    values (char(current_date - 7 days))
    
    1
    ----------
    04/20/2010

    You need to standardize it so it will always return the same value: char(current_date - 7 days,ISO)

    Code:
    values (char(current_date - 7 days,ISO))
    
    1
    ----------
    2010-04-20
    
      1 record(s) selected.

    Andy

  3. #3
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    DB2 Version 9.5 for Linux, UNIX, and Windows SQL Reference, Volume 1

    Chapter 3. Functions

    INTEGER

    ...
    ...

    date-expression
    An expression that returns a value of the DATE data type. The result is an
    INTEGER value representing the date as yyyymmdd.
    You can replace
    cast(replace(char(current_date - 7 days),'-','') as integer)
    with
    INTEGER(current_date - 7 days)

  4. #4
    Join Date
    Apr 2010
    Posts
    5

    Solved

    Thanks you for ARWinner

  5. #5
    Join Date
    Apr 2010
    Posts
    5
    yes! yes! ...the problem is SOLVED!!!
    Last edited by aankdavid; 04-28-10 at 01:34.

Tags for this Thread

Posting Permissions

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