Results 1 to 2 of 2

Thread: db2 SQL Query

  1. #1
    Join Date
    Mar 2003

    Unanswered: db2 SQL Query

    I am running db2 v 7.2 under OS 390. I need to perform a sql query which satisfy my bellow requirement.

    Have a table with fields appNo, appTimeStamp,appStatus

    and has fields like,

    appNo appTimestamp appStatus

    1 2004-04-04-12.00.00 START
    1 2004-04-04-12.04.00 COMPLETE
    1 2004-04-04-12.12.00 HIRED

    has any app numbers with like the above status..

    My query is to fetch the records,

    1. Select count of all appNo's (distinct) which the appTimestamp should be less than 4 minutes for the appStatus between "START" and "COMPLETE".

    Any thoughts?

  2. #2
    Join Date
    May 2003
    FROM table-name a
    WHERE a.status = 'START'
    AND exists
    (SELECT b.appNo
    FROM table-name b
    WHERE b.appNo = a.appNo
    AND b.status = 'COMPLETE'
    AND (b.appTimestamp - 4 minutes) < a.appTimestamp )
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

Posting Permissions

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