Results 1 to 3 of 3
  1. #1
    Join Date
    Sep 2003
    Posts
    15

    Question Unanswered: Form a query using TOP and fetch next Records

    Hi,
    I have an INI file, contans a fiel with "RestrictedRead =100"
    I am reading the INI file in my Java code and getting this restrictd value and building a query dynamically for further processsing as...

    sSQL = sSQL + " TOP " + getRestrictReadValue() + " ";

    PROBLEM

    If I have 10000 records to be processed, as of now it is reading only 100 records.But I want after 100 records completed it should fetch next 100 and continue till EOF(10000) records...

    Can anyone help me how to succeed this and how to form a query for this scenario.

    Matter very urgent

    Cheers
    Ravi

  2. #2
    Join Date
    Sep 2003
    Location
    The extremely Royal borough of Kensington, London
    Posts
    778
    I dont know the meaning of "TOP" in this context.

    If you will retrive all records into a single result set then use the following method. However if you only want to return 100 per result set then the second option will work, although the first is clearly faster and more efficent.

    From 1.4 -
    "boolean absolute(int row)
    Moves the cursor to the given row number in this ResultSet object"

    This is ideal for your problem and its also faster than the SQL query, simply set row to row + limit.

    Here is the SQL query alternative anyway,

    The final row of ResultSet will contain the last rownum returned. Repeat this procedure until EOF is reached.

    X = X + (rownum from final row) ' set X to 0 for first call
    limit = 100

    Query,

    select columns
    from
    (
    select *
    from table
    minus
    select *
    from table
    where rownum <= X)
    where rownum <= limit;
    Last edited by r123456; 12-06-03 at 22:19.
    Bessie Braddock: Winston, you are drunk!
    Churchill: And Madam, you are ugly. And tomorrow, I'll be sober, and you will still be ugly.

  3. #3
    Join Date
    Sep 2003
    Location
    The extremely Royal borough of Kensington, London
    Posts
    778
    Update -
    A solution to your problem using the ResultSet absolute method has been included in my previous reply. It's the efficent solution. If you do need to use multiple instances of ResultSet, then perhaps some PL/SQL in conjunction with a fast query.
    Last edited by r123456; 12-05-03 at 10:23.
    Bessie Braddock: Winston, you are drunk!
    Churchill: And Madam, you are ugly. And tomorrow, I'll be sober, and you will still be ugly.

Posting Permissions

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