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

Advertisements

About davidand

Hi, welcome to my blogs. My name is David Andriansyah. I am currently working as an independent Cognos Business Intelligence consultant, and I also as Freelance Web Developer. View all posts by davidand

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: