Results 1 to 5 of 5
  1. #1
    Join Date
    Feb 2006
    Posts
    11

    Unanswered: Problem in Fetch First Clause

    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

  2. #2
    Join Date
    Dec 2005
    Posts
    39
    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;

  3. #3
    Join Date
    Feb 2006
    Posts
    11
    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

  4. #4
    Join Date
    Aug 2003
    Posts
    106
    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!

    You are the creator of your own destiny!

  5. #5
    Join Date
    Feb 2006
    Posts
    11
    Quote Originally Posted by db2guru1
    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

Posting Permissions

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