Help with Query

andrevanzyl · #1 (**permalink**) 10-07-09, 06:19

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

rahul_s80 · #2 (**permalink**) 10-07-09, 06:33

chk about union

tonkuma · #3 (**permalink**) 10-07-09, 07:09

PLEASE see my answer on this thread.

Query to fetch first and last row

andrevanzyl · #4 (**permalink**) 10-07-09, 10:05

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

tonkuma · #5 (**permalink**) 10-07-09, 10:30

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)

tonkuma · #6 (**permalink**) 10-07-09, 10:37

UNION ALL will be better than UNION.

tonkuma · #7 (**permalink**) 10-07-09, 10:45

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.

Lenny77 · #8 (**permalink**) 10-07-09, 11:24

Production problem ?

Lenny

andrevanzyl · #9 (**permalink**) 10-08-09, 04:41

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

tonkuma · #10 (**permalink**) 10-08-09, 07:18

Is your server working on z/OS?

tonkuma · #11 (**permalink**) 10-08-09, 07:37

You can use MIN and MAX column functions and UNION ALL,
like Lenny's sample(replace rowid with COMPANY_CODE) in this thread:
Query to fetch first and last row

andrevanzyl · #12 (**permalink**) 10-08-09, 08:02

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

Mathew_paul · #13 (**permalink**) 10-08-09, 08:49

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

tonkuma · #14 (**permalink**) 10-08-09, 09:00

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.

dav1mo · #15 (**permalink**) 10-08-09, 09:57

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

Internet Services

Web Development

Digital Marketing

Consumer Tech