Data Filtering current_date -7 days

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;

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

The problem is the following expression: char(current_date – 7 days)
It will be interpreted differently on different environments

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.

Thank you very much ARWinner (Andy)

reference: www.dbforums.com

Advertisement