Results 1 to 2 of 2

Thread: Help with DB2 Syntax

  1. #1
    Join Date
    Jul 2003
    Location
    South Africa
    Posts
    1

    Question Help with DB2 Syntax

    Hi Everyone,

    Recently our comany upgraded their applications from
    MS ACCESS 2000 to DB2 version 7.2 UDB. I'm not as
    skilled in DB2 with the syntax.

    I had a few sql queries in Accesss that i just cannot
    convert to DB2, if there is anyone who can help please
    let me know.

    I can get the query to output the results like this:

    serv_hostname year month disk_drive value
    -------------- ---- ----- ------------ ---------
    megaweb1 2003 Mar C: 86.073
    megaweb1 2003 Mar D: 88.603
    megaweb1 2003 Apr C: 85.958
    megaweb1 2003 Apr D: 84.425
    megaweb1 2003 May C: 85.844
    megaweb1 2003 May D: 82.293
    megaweb1 2003 Jun C: 85.292
    megaweb1 2003 Jun D: 75.189

    But I need in in the format below: (this is done in access
    using the TRANSFORM and PIVOT statements.)

    serv_hostname year month C: D:
    -------------- ---- ----- ------ ------
    megaweb1 2003 Mar 86.073 88.603
    megaweb1 2003 Apr 85.958 84.425
    megaweb1 2003 May 85.844 82.293
    megaweb1 2003 Jun 85.292 75.189

    Any help will be greatly appreciated.

    Thanks,
    Sharad.

  2. #2
    Join Date
    Aug 2003
    Location
    Moscow, Russia
    Posts
    3

    Lightbulb

    Lets name first query (columns serv_hostname, year, month, disk_drive, value) as QRY.

    SELECT serv_hostname, year, month, SUM(CASE WHEN disk_drive='c:' THEN value ELSE 0 END) AS C, SUM(CASE WHEN disk_drive='D:' THEN value ELSE 0 END) AS D FROM QRY GROUP BY serv_hostname, year, month

    (Sample is actual for DB2/400, may work on PC-version too.)
    Last edited by moron; 08-07-2003 at 05:30 AM.

Posting Permissions

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