Results 1 to 15 of 15

Thread: Help with Query

  1. #1
    Join Date
    Oct 2009
    Posts
    8

    Unanswered: Help with Query

    Hi All

    I am trying to fetch data in a specific way, which I will do my best to explain. Basically I need to fetch two rows of data, each with a specific "COMPANY_CODE". So far I have the following two queries:

    1. SELECT COMPANY_CODE, MASTER_ID, PLAN_CODE FROM GS16.CONTT ORDER BY COMPANY_CODE DESC FETCH FIRST 1 ROWS ONLY

    This brings back: COMPANY_CODE MASTER_ID PLAN_CODE
    OMG 70245000064488 EMGY

    and

    2. SELECT COMPANY_CODE, MASTER_ID, PLAN_CODE FROM GS16.CONTT ORDER BY COMPANY_CODE ASC FETCH FIRST 1 ROWS ONLY

    This brings back: COMPANY_CODE MASTER_ID PLAN_CODE
    NAM MFNAM0000417464 MMANAM

    What I need is a single query that will fetch data that looks like this:

    COMPANY_CODE MASTER_ID PLAN_CODE
    OMG 770245000064488 EMGY
    NAM MFNAM0000417464 MMANAM

    Any help will be greatly appreciated.

    Thank You
    Andre van Zyl

  2. #2
    Join Date
    Jul 2006
    Location
    Pune , India
    Posts
    433
    chk about union
    Rahul Singh
    Certified DB2 9 DBA / Application Developer

  3. #3
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    PLEASE see my answer on this thread.

    http://www.dbforums.com/db2/1648632-...-last-row.html
    Last edited by tonkuma; 10-07-09 at 08:41.

  4. #4
    Join Date
    Oct 2009
    Posts
    8
    Hi All

    Thank you for your quick replies...

    I have tried your solutions but receive errors back.

    I receive an error using the following query(see attachment forum.bmp)...

    SELECT COMPANY_CODE, MASTER_ID, PLAN_CODE FROM (SELECT COMPANY_CODE, MASTER_ID, PLAN_CODE, ROWNUMBER() OVER(ORDER BY COMPANY_CODE ASC) AS rn_asc, ROWNUMBER() OVER(ORDER BY COMPANY_CODE DESC) AS rn_desc FROM GS16.CONTT WHERE COMPANY_CODE IN ('NAM','OMG')) WHERE rn_asc = 1 OR rn_desc = 1

    I also tried the UNION as below but also received an error(see attachment forum1.bmp)

    SELECT COMPANY_CODE, MASTER_ID, PLAN_CODE FROM GS16.CONTT ORDER BY COMPANY_CODE DESC FETCH FIRST 1 ROWS ONLY UNION SELECT COMPANY_CODE, MASTER_ID, PLAN_CODE FROM GS16.CONTT ORDER BY COMPANY_CODE ASC FETCH FIRST 1 ROWS ONLY

    Thanks in advance
    Andre van Zyl
    Attached Thumbnails Attached Thumbnails forum.bmp   forum1.bmp  

  5. #5
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    What platforn and DB2 version/release are you using?

    Anyhow, please try :
    SELECT COMPANY_CODE, MASTER_ID, PLAN_CODE FROM (SELECT COMPANY_CODE, MASTER_ID, PLAN_CODE, ROWNUMBER() OVER(ORDER BY COMPANY_CODE ASC) AS rn_asc, ROWNUMBER() OVER(ORDER BY COMPANY_CODE DESC) AS rn_desc FROM GS16.CONTT WHERE COMPANY_CODE IN ('NAM','OMG')) q WHERE rn_asc = 1 OR rn_desc = 1

    (SELECT COMPANY_CODE, MASTER_ID, PLAN_CODE FROM GS16.CONTT ORDER BY COMPANY_CODE DESC FETCH FIRST 1 ROWS ONLY) UNION (SELECT COMPANY_CODE, MASTER_ID, PLAN_CODE FROM GS16.CONTT ORDER BY COMPANY_CODE ASC FETCH FIRST 1 ROWS ONLY)

  6. #6
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    UNION ALL will be better than UNION.

  7. #7
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Please format your sql code with new lines and indentions.

    SQL is a programming language.
    I can't imagine C or Java programmer who don't format his/her code with new lines and indentions, except very novice programmer.

  8. #8
    Join Date
    Jul 2009
    Location
    NY
    Posts
    963
    Production problem ?

    Lenny

  9. #9
    Join Date
    Oct 2009
    Posts
    8
    Hi

    We are using version 8.1. I have tried the new code that I received but am still getting errors. It is the same error for the ROWNUMBER query but get a different one for the UNION ALL(see attachment). The code I used is below, I hope the format is correct(apologies for not using the correct format before, as you guessed I am a novice )

    SELECT COMPANY_CODE,
    MASTER_ID,
    PLAN_CODE
    FROM (SELECT COMPANY_CODE,
    MASTER_ID,
    PLAN_CODE,
    ROWNUMBER() OVER(ORDER BY COMPANY_CODE ASC) AS rn_asc,
    ROWNUMBER() OVER(ORDER BY COMPANY_CODE DESC) AS rn_desc
    FROM GS16.CONTT
    WHERE COMPANY_CODE IN ('NAM','OMG')) q
    WHERE rn_asc = 1
    OR rn_desc = 1

    ---------------------------------

    (SELECT COMPANY_CODE,
    MASTER_ID,
    PLAN_CODE
    FROM GS16.CONTT
    ORDER BY COMPANY_CODE DESC
    FETCH FIRST 1 ROWS ONLY)
    UNION ALL
    (SELECT COMPANY_CODE,
    MASTER_ID,
    PLAN_CODE
    FROM GS16.CONTT
    ORDER BY COMPANY_CODE ASC
    FETCH FIRST 1 ROWS ONLY)

    Thanks again for all your help.
    Andre van Zyl
    Attached Thumbnails Attached Thumbnails unionerror.bmp  

  10. #10
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Is your server working on z/OS?

  11. #11
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    You can use MIN and MAX column functions and UNION ALL,
    like Lenny's sample(replace rowid with COMPANY_CODE) in this thread:
    http://www.dbforums.com/db2/1648632-...-last-row.html

  12. #12
    Join Date
    Oct 2009
    Posts
    8
    Hi

    The server is working on z/OS. I tried Lennys sample and it brought back many rows of data instead of just two(one row with COMPANY_CODE = NAM and the other with COMPANY_CODE = OMG). The code is below...

    SELECT T1.*
    FROM GS16.CONTT T1
    JOIN
    (SELECT MIN(COMPANY_CODE) MID
    FROM GS16.CONTT
    UNION ALL
    SELECT MAX(COMPANY_CODE) MID
    FROM GS16.CONTT) T2
    ON T1.COMPANY_CODE = T2.MID
    ORDER BY T1.COMPANY_CODE

    Thanks & Regards
    Andre

  13. #13
    Join Date
    Oct 2007
    Posts
    246
    try this as tonkuma said
    (SELECT COMPANY_CODE, MASTER_ID, PLAN_CODE, row_number() over(order by company_code desc) as m_desc FROM GS16.CONTT FETCH FIRST 1 ROWS ONLY)
    union all
    (SELECT COMPANY_CODE, MASTER_ID, PLAN_CODE row_number() over(order by company_code asc) as m_asc FROM GS16.CONTT FETCH FIRST 1 ROWS ONLY)
    regds
    Paul

  14. #14
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    I understood your issue.
    DB2 for z/OS Version 8 does not support ORDER BY and FETCH FIRST n ROWs ONLY clause in a subselect nor OLAP functions.
    They are supported on DB2 for z/OS Version 9.

    Please try:
    Code:
    SELECT COMPANY_CODE, MASTER_ID, PLAN_CODE
      FROM GS16.CONTT a
     WHERE
           NOT EXISTS
           (SELECT *
              FROM GS16.CONTT b
             WHERE
                   b.COMPANY_CODE > a.COMPANY_CODE
               OR
                   b.COMPANY_CODE = a.COMPANY_CODE
               AND b.MASTER_ID    > a.MASTER_ID
               OR
                   b.COMPANY_CODE = a.COMPANY_CODE
               AND b.MASTER_ID    = a.MASTER_ID
               AND b.PLAN_CODE    > a.PLAN_CODE
           )
       OR
           NOT EXISTS
           (SELECT *
              FROM GS16.CONTT b
             WHERE
                   b.COMPANY_CODE < a.COMPANY_CODE
               OR
                   b.COMPANY_CODE = a.COMPANY_CODE
               AND b.MASTER_ID    < a.MASTER_ID
               OR
                   b.COMPANY_CODE = a.COMPANY_CODE
               AND b.MASTER_ID    = a.MASTER_ID
               AND b.PLAN_CODE    < a.PLAN_CODE
           )
    ;
    But, this query may not work effectively.
    Two queries in your first post might be a practical solution.

  15. #15
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    You could try something like this:

    Code:
    SELECT COMPANY_CODE
             , MASTER_ID
             , PLAN_CODE
        FROM GS16.CONTT a
    where   a.COMPANY_CODE = (select min(b. COMPANY_CODE)
                                            FROM GS16.CONTT b)
    union all
    SELECT COMPANY_CODE
             , MASTER_ID
             , PLAN_CODE
        FROM GS16.CONTT c
    where   c.COMPANY_CODE = (select max(d. COMPANY_CODE)
                                            FROM GS16.CONTT d)
    Dave Nance

Posting Permissions

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