Results 1 to 5 of 5
  1. #1
    Join Date
    Feb 2002
    Location
    mumbai, india
    Posts
    5

    Unanswered: alternative of rownum in DB2

    Can any body suggest the alternative of oracle rownum in DB2.

    I am using rownum for getting the prerequired number of the row froma query.

    Ex.

    Select * from tab1 where rownum < 3

    The 'fetch first n rows only' doesnt works in stored procedure. So please suggest some other alternative or suggest the use of 'first n rows only' in stored procedure.

    Thanx!

  2. #2
    Join Date
    Oct 2001
    Location
    Bangalore
    Posts
    186
    Hi,
    Try this out:

    SELECT * FROM (SELECT col1,col2,col3, rownumber() over() AS rn FROM tab1) as tr WHERE rn<3;

    where col1,col2... will be the column names for tab1 table.

    -Prashant G Dahalkar
    Prashant

  3. #3
    Join Date
    Jul 2003
    Posts
    1

    Rownumber() doesn't work with AND in condition

    This query doesn't run if it is appended by AND i.e. if u have more than 1 condition then it doesn't count the number of rows.

    e.g.
    try
    SELECT * FROM (SELECT users_id, rownumber() over() AS rn FROM am_customer) as tr WHERE users_id=52612 and rn<3

    This doesn't retutn correct no. of records. 'rn' count for primary key values and not number of rows.

  4. #4
    Join Date
    Jan 2002
    Location
    Manila, Philippines
    Posts
    71
    Hi vijayanand !

    What do you mean by "FETCH FIRST n ROWS ONLY doesn't work in stored procedure. It is !

    Can you give more details so I can help ei. how do you create SP, your table struct and sql statement

  5. #5
    Join Date
    Jun 2003
    Location
    Canada
    Posts
    37

    Re: alternative of rownum in DB2

    Originally posted by vijayanand
    The 'fetch first n rows only' doesnt works in stored procedure.
    Hi vijayanand,

    Can you please provide the error message and some environment information?

    Problems with FETCH FIRST n ROWS ONLY have been known to exist on multiple platforms and have been fixed in various FPs - I am not aware of limitations with SP in general.

    Julius

Posting Permissions

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