I have tried providing FETCH FIRST clause for each select query seperated by a UNION but it is giving me an Error saying that illegal use of keywork UNION. The test query which i had used is given below

(SELECT XXXXXX FORM XXXXX
FETCH FIRST 10 ROWS ONLY)
UNION
(SELECT XXXXXX FORM XXXXX
FETCH FIRST 10 ROWS ONLY)
WITH UR;

Now here if we omit the FETCH FIRST clause the query runs fine. Is there any other way to include FETCH FIRST clause in a select query seperated by union.

Thanks for your time.

Nouman Memon
Patni Computer's

I dont think you would need fetch clause for individual selects. you can try this -

SELECT XXXXXX FROM XXXXX
UNION
SELECT XXXXXX FROM XXXXX
FETCH FIRST 10 ROWS ONLY
WITH UR;

Yes its right but if there are more than 1,00,000 records in the respective table and if we want the output in a specific order, so here we might have to include an ORDER BY clause. Now if we use only one FETCH FIRST Clause and also a Order By clause all the records in the table will be proccessed and will sorted in specific order and out of these ordered records only FIRST 10 records will be selected. So here the processing time will be increased. Is there any way to process only 10 records and order only that 10 records as given below

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

(Is there any subsitute way to achive above query as the above query is giving me an error)

Thanks for your reply.

Nouman Memon
Patni Computer's

This works for SAMPLE database!

(select * from department fetch first 2 rows only)
union
(select * from department fetch first 3 rows only)
order by 2 desc with ur

Make sure they have the brackets as above and make sure you spell FROM correctly!

Hi,
I the invalid FROM was just a typo, i am sorry for that. I was trying the same thing in my Mainframe DB2 environment but still it wasnt workning. Its giving me an ERROR on UNION clause.

Thanks for your reply.

Nouman Memon
Patni computers