Hi,

We are using DB2 v8.1.3 on Win2k.

The following query is obtaining all the rows that meet the criteria.

select col1, max(col2) from
<table_name>
where (month(current_timestamp - last_upd) = 6)
group by collab_id, track_id


Now the issue is; we need to extract all rows that are >= 6 months old..

Could you please tell me why the second query is not fetching any rows?

select col1, max(col2) from
<table_name>
where (month(current_timestamp - last_upd) = 6) OR (month(current_timestamp - last_upd) > 6)
group by collab_id, track_id

Any help is greatly appreciated.

The result of subtracting two timestamps is a Timestamp Duration.
This is a DECIMAL(20,6) datatype in the format yyyymmddhhmmss.zzzzzz showing years, months, days, hours minutes seconds, and microseconds. Using this knowledge your SQL could be:

select col1, max(col2) from
<table_name>
where ((current_timestamp - last_upd) >= DECIMAL(600000000,20,6))
group by collab_id, track_id

HTH

Andy

You can also do it this way:

select col1, max(col2) from
<table_name>
where (last_upd <= (current timestamp - 6 months))
group by collab_id, track_id

HTH

Andy [/SIZE][/QUOTE]

Thank you for your quick response. They all are very good.

Meanwhile, the issue seems to be that if I issue the query from db2 command window - the dos shell does not recognize ">=" condition and hence the error. The same query however, runs just fine using command center!! Strange ........but, true.

Many thanks! once again.

Hi,

In Command Window the ">" character means redirect all what was written left of the character to the file. This is operating system command! So you probably generated many files...

To use ">" charater in Command Window you have to use double quotes.

Sample:
db2 "select * from x where a > 1"
The above command is OK.

db2 select * from x where a > 1
The above command writes "something" to the file with name "1".

Also be carful with "<" and "|" etc character. My tip is: always write commands with double quotes in Command Window.

Hope this helps,
Grofaty