Results 1 to 5 of 5
  1. #1
    Join Date
    Sep 2003
    Posts
    218

    Unanswered: greater than, equal to condition

    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.

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    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

  3. #3
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    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]

    Originally posted by ARWinner
    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

  4. #4
    Join Date
    Sep 2003
    Posts
    218

    greater than, equal to condition

    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.

  5. #5
    Join Date
    Jan 2003
    Posts
    1,605
    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
    Last edited by grofaty; 10-20-03 at 08:10.

Posting Permissions

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