If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

 
Go Back  dBforums > Database Server Software > DB2 > Fetch First in DB2 mainframe

Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old
Registered User
 
Join Date: Feb 2006
Posts: 11
Post Fetch First in DB2 mainframe

Hi,
I need to execute 2 select clause seperated by a union clause. These two select should fetch only first 10 rows (in order to save the search time). I also want to sort them in speciifc order (desc). The query which i was trying on my mainframe terminal (SPUFI DB2) is given below

(SELECT * FROM XXXXX FETCH FIRST 10 ROWS ONLY)
UNION
(SELECT * FROM XXXXX FETCH FIRST 10 ROWS ONLY)
ORDER BY 1 DESC WITH UR;

But this query is not working (giving an error). Can any body help to suggest any subsitute query for this

(Note that the search time is very imp as there are lacs of records)

Thanks for your time.

Nouman Memon
Patni computers
Reply With Quote
  #2 (permalink)  
Old
Registered User
 
Join Date: Jan 2003
Posts: 1,595
Hi,
The first 10 rows only can only be used at the and of the SQL statement.

Which mainfraime system do you use: db2/vse, db2/vm, db2/zOs, db2/os-400, db2-linux390?

Grofaty
Reply With Quote
  #3 (permalink)  
Old
Registered User
 
Join Date: Mar 2005
Posts: 7
could you try this?

It might be helpfull if you provide more information regarding the error (sqlcode?)

Anyways, using union together with fetch first might give you unexpected results. Although i'm not sure on mainframe though...

Could you try executing the folowing statements and share your results here?
1.
Code:
values ('A',1),('A',1) 
UNION
values ('B',1),('B',2),('B',3) 
UNION
values ('A',1)
UNION
values ('A',2)
UNION
values ('A',2)
UNION
values ('C',1)
should return 6 rows
A 1
B 1
C 1
A 2
B 2
B 3

2.
Code:
(values ('A',1),('A',1) fetch first row only)
UNION
(values ('B',1),('B',2),('B',3) fetch first 2 rows only)
UNION
values ('A',1)
UNION
values ('A',2)
UNION
values ('A',2)
UNION
values ('C',1)
order by 1 desc with ur
as per your example also returns as expected (no errors on my system with this one)

however
3.
Code:
values ('A',1),('A',1)  
UNION
values ('B',1),('B',2),('B',3) 
UNION
values ('A',1)
UNION
values ('A',2)
UNION
values ('A',2)
UNION
values ('C',1)
fetch first 3 rows only
Does NOT return the expected 3 rows (but less)

Furthermore, order by cannot be used together with fetch first
4.
Code:
values ('C',1) fetch first row only
order by 1 desc with ur
will fail, whereas
5.
Code:
(values ('C',1) fetch first row only)
order by 1 desc with ur
will execute fine

So could it be, you misplaced a bracket somewhere?

regards
ihendr
Reply With Quote
  #4 (permalink)  
Old
Registered User
 
Join Date: Oct 2005
Posts: 109
ihendr, grofaty is correct:
it is really the platform-syntax difference:
on LUW the FETCH FIRST is part of a subselect and thus allowed with UNION,
an z/OS it is allowed only once at the end of a select statement.
__________________
Juliane
Reply With Quote
  #5 (permalink)  
Old
Registered User
 
Join Date: Feb 2006
Posts: 11
Hi,
Is there any way of fetching only required no of records in z/OS for subselect queries seperated by a UNION.

Thanks for prevoius replies

Nouman Memon
Patni Computers
Reply With Quote
  #6 (permalink)  
Old
Super Moderator
 
Join Date: Aug 2001
Location: UK
Posts: 4,649
what version of db2 are you running, v2 ????
__________________
Visit the new-look IDUG Website , register to gain access to the excellent content.
Reply With Quote
  #7 (permalink)  
Old
Registered User
 
Join Date: Sep 2004
Location: Belgium
Posts: 1,126
The following approach should work (apart from the fact that UNION would remove duplicates, while here the second INSERT might fail):
Code:
CREATE TABLE MYTMP LIKE XXXXX;
INSERT INTO MYTMP
   SELECT * FROM XXXXX FETCH FIRST 10 ROWS ONLY WITH UR;
INSERT INTO MYTMP
   SELECT * FROM XXXXX FETCH FIRST 10 ROWS ONLY WITH UR;
SELECT * FROM MYTMP ORDER BY 1 DESC;
DROP TABLE MYTMP;
__________________
--_Peter Vanroose,
__IBM Certified Database Administrator, DB2 9 for z/OS
__IBM Certified Application Developer
__ABIS Training and Consulting
__http://www.abis.be/

Last edited by Peter.Vanroose; 02-20-06 at 05:04.
Reply With Quote
  #8 (permalink)  
Old
Registered User
 
Join Date: Jan 2003
Posts: 1,595
Hi,

don't know if row_number function is supported on DB2/zOS, but try the following:

SELECT COL1, COL2, COL3 FROM (
SELECT ROW_NUMBER () OVER () AS COUNT, COL1, COL2, COL3 FROM XXX
) AS TEMP WHERE COUNT <=10
UNION ALL
SELECT...

Hope this helps,
Grofaty
Reply With Quote
  #9 (permalink)  
Old
Registered User
 
Join Date: Jan 2003
Posts: 1,595
Quote:
Originally Posted by nouman
Hi,
Is there any way of fetching only required no of records in z/OS for subselect queries seperated by a UNION.

Thanks for prevoius replies

Nouman Memon
Patni Computers
Hi,
on DB2/LUW fetch first n rows is supported beggining version 8.1. It was not supported on v7.2 and before.

Thanks,
Grofaty
Reply With Quote
Reply

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On