|
View previous topic :: View next topic
|
| Author |
Message |
Ananya
New User
Joined: 26 Feb 2004
Posts: 19
Location: Chennai
|
|
|
|
Dear All!!!
1. How do you use MULTIPLE CURSORS for a single table?
Imagine a scenario like this ...
A Result Set has 30 Rows returning to the application program. Now I want these things to happen.... (Open Multiple Cursors)
a. FIRST CURSOR should fetch first 10 rows.
b. SECOND CURSOR should fetch then next 10 rows (i.e., 11 - 20)
c. THIRD CURSOR should fetch the last 10 rows (21 - 30)
Now my question is how do I point to different locations in the Result Set with 3 different Cursors???
I'm not sure if this is really possible.
Pls do help me.
Views are appreciated.
Thanks in advance!!
Cheers
-Ananya |
|
| Back to top |
|
 |
mdtendulkar
Active User
Joined: 29 Jul 2003
Posts: 237
Location: USA
|
|
|
|
Hello Ananya,
Your requirement is non-standard. As per normal processing this can not be done.
Following are the ways to do it:
1) You can use only one cursor and process the specific number of records in paragraphs. say 1 to 15 in Para-1, 16-30 in Para-2 and remaining 15 records in Para-3.
In this way you can direct the processing in your desired manner.
2) The other way to do is as below:
| Quote: |
a) Get the number of records from the table for the query.
SELECT COUNT(*) FROM <TABLE-NAME> WHERE <WHERE-CONDITION>
b) Divide the count by 3 and get the integer records
c) In first Cursor by using FETCH FIRST N ROWS clause fetch the COUNT/3 records in ASCENDING order
d) In third Cursor by using FETCH FIRST N ROWS clause fetch the COUNT/3 records in DESCENDING order
(Here the last record will come first and first record will come last)
e) Now that you have first N/3 and last N/3 records there will be middle N/3 records remaining. These records can be retrieved by using the LAST KEY of both the cursors. (Last record's KEY of both the cursors)
SELECT * FROM <TABLE-NAME> WHERE KEY > KEY1 and KEY < KEY3
Here KEY1 = Last record of the first cursor.
KEY3 = Last record of third cursor.
|
I have not tried this solution, but you can try this out.
Hope this helps,
Regards
Mayuresh Tendulkar |
|
| Back to top |
|
|
|
|
