Results 1 to 12 of 12
  1. #1
    Join Date
    Oct 2002
    Posts
    34

    Unanswered: Newb? : DB2 equiv of Top?

    What is the DB2 equivalent of SQL Server's Top? I want to return a set number of rows, like the first 10 rows, regardless of how many are normally returned by the query.
    TIA

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    i believe the syntax is

    select foo, bar
    from yourtable
    order by foo descending
    fetch first 10 rows only


    rudy
    http://r937.com/

  3. #3
    Join Date
    Sep 2002
    Posts
    456

    Re: Newb? : DB2 equiv of Top?

    Try this..

    select * from test fetch first 10 rows only;

    dollar

    Originally posted by elomon
    What is the DB2 equivalent of SQL Server's Top? I want to return a set number of rows, like the first 10 rows, regardless of how many are normally returned by the query.
    TIA

  4. #4
    Join Date
    Oct 2002
    Posts
    34
    I tried:
    select * from mytable fetch first 10 rows only

    The error was:

    -2147467259:[IBM][CLI Driver][DB2] SQL0199N The use of the reserved word "FETCH" following "" is not valid. Expected tokens may include: "FOR WITH ORDER UNION EXCEPT QUERYNO OPTIMIZE ". SQLSTATE=42601

    Did I miss something?

  5. #5
    Join Date
    Sep 2002
    Posts
    456
    can you send me your full query?

    dollar

    Originally posted by elomon
    I tried:
    select * from mytable fetch first 10 rows only

    The error was:

    -2147467259:[IBM][CLI Driver][DB2] SQL0199N The use of the reserved word "FETCH" following "" is not valid. Expected tokens may include: "FOR WITH ORDER UNION EXCEPT QUERYNO OPTIMIZE ". SQLSTATE=42601

    Did I miss something?

  6. #6
    Join Date
    Oct 2002
    Posts
    34
    The exact query, with tablenames intact:



    select * from D396DA.PS_RC_CASE fetch first 10 rows only

  7. #7
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    try it with an ORDER BY clause


    rudy

  8. #8
    Join Date
    Oct 2002
    Posts
    34
    Still no joy:

    select case_id from D396DA.PS_RC_CASE
    Order by case_id
    fetch first 10 rows only

    Same Error

  9. #9
    Join Date
    Sep 2002
    Posts
    456
    hmm...the same query with different table works fine on my machine!!!
    which DB2 version you are running on and on which OS?

    dollar

    Originally posted by elomon
    Still no joy:

    select case_id from D396DA.PS_RC_CASE
    Order by case_id
    fetch first 10 rows only

    Same Error

  10. #10
    Join Date
    Oct 2002
    Posts
    34
    DB2 Version is 7 something, OS is mainframe (hopefully that isn't a totally stupid thing to say).

  11. #11
    Join Date
    Oct 2002
    Posts
    34
    I found another site that says 'Your cursor must have been declared scrollable' in ver 7.

    How would I do that?

    TIA

  12. #12
    Join Date
    Oct 2002
    Posts
    34
    Question is withdrawn. The fetch still doesn't work and I'm just going to deal with the extra rows with external program.
    thanks to all for trying.

Posting Permissions

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