|
View previous topic :: View next topic
|
| Author |
Message |
sendhilkumaran
New User
Joined: 15 Apr 2010
Posts: 8
Location: chennai
|
|
|
|
Hi Experts,
I have a requirement is like below:
I am using a scroll cursor to fetch records based on some conditions. Now, for the randomly fetched row I need to know the the position of the that row in the rowset. (I know that given a position of a row I can fetch that correponding row using scroll cursor; but what I need is the reverse, I know the row but I want the position in the rowset)
Anybody could help me on this. |
|
| Back to top |
|
 |
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006
Posts: 19244
Location: Inside the Matrix
|
|
|
|
Hello,
It may help someone help you if you explain why this is needed? How will the position be used? |
|
| Back to top |
|
|
sendhilkumaran
New User
Joined: 15 Apr 2010
Posts: 8
Location: chennai
|
|
|
|
Hi,
The stored procedure was having a paging logic. Now my requirement is like if the user enters the random record, say employee number, then i need to fetch the entire page where the employee number lies.
Say, my search employee number was the 155 th record and if my page has 100 rows each. The Employee number belongs to 2 nd page. So If I know the position of the record in the row set, then only I can fetch the entire second page. |
|
| Back to top |
|
|
dbzTHEdinosauer
Global Moderator
Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne
|
|
|
|
I imagine that any suggestion I make will be vetoed,
since I imagine that you can not modify the SP.
so, what I will now suggest to you is:
you are miss-using the SP.
Why read 200 rows when you want a specific row?
If the user wants a specific row, then read 1 row! |
|
| Back to top |
|
|
sendhilkumaran
New User
Joined: 15 Apr 2010
Posts: 8
Location: chennai
|
|
|
|
Hi dbzTHEdinosauer,
Thanks for your reply. I too asked the question what you have asked me? But the user wanted to have such a functionality.
Actually my SP should have the following logic
1. I need to display the records in Pages, if the user doesn't specifies any employee number. Not always the user knows the employee number. So it required to have this function.
2. If the user specifies the employee number, then I need to display the entire page where the record lies and not the single record. This I confirmed with the users.
I am right thinking of using rownumber() over() function to locate the position of that record. Then I will continue the existing paging logic.
Have a look at my query to find the position
| Code: |
SELECT Z.ROW_NUM FROM
(
SELECT EMP_NUM,ROWNUMBER() OVER(ORDER BY EMP_NUM ASC) AS ROW_NUM
FROM MYTABLE
FETCH FIRST 10 ROWS ONLY)Z
WHERE Z.EMP_NUM= '1990'
WITH UR;
|
But I am not sure how far this will be consistent as I will be using a using different query to find the position and a different one for the paging logic.
Please let me know your comments |
|
| Back to top |
|
|
dbzTHEdinosauer
Global Moderator
Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne
|
|
|
|
not knowing anything about your tables, user or environment,
it is hard to give advice.
| Quote: |
| 2. If the user specifies the employee number, then I need to display the entire page where the record lies and not the single record. |
this sounds like ISPF CUROR DOWN, which would be mimic'd by a where condition of employee_no => host-variable.
as with any paging logic, you need to always save the:- top-of-page key
- bottom-of-page key
- number of items on page
- page number
this kind of info you could store in a cobol internal table and refer to/refresh/add-to as your program moves along.
If you are a CICS module driving a green-screen (native cics screen), you may have to resort to a dfhcommarea or TSQ.
If your are being driven by a web-front end, and have control over the front end logic, the front end should store this data.
I hate to say it, but it does not sound as if you have fully planned this.
Your question sounds as if you are trying to solve a problem,
that you did not plan for,
and are jimmy-riggin' the existing code to make it work.
my suggestion is to insure that you don't cut corners, you will get bit, believe me. |
|
| Back to top |
|
|
|
|
