Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups Profile Log in to check your private messages Log in
 

 

Need to find out a row position in a rowset after fetching?

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
sendhilkumaran

New User


Joined: 15 Apr 2010
Posts: 8
Location: chennai

PostPosted: Wed Aug 04, 2010 6:12 pm    Post subject: Need to find out a row position in a rowset after fetching?
Reply with quote

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
View user's profile Send private message

dick scherrer

Site Director


Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

PostPosted: Wed Aug 04, 2010 9:50 pm    Post subject:
Reply with quote

Hello,

It may help someone help you if you explain why this is needed? How will the position be used?
Back to top
View user's profile Send private message
sendhilkumaran

New User


Joined: 15 Apr 2010
Posts: 8
Location: chennai

PostPosted: Thu Aug 05, 2010 11:26 am    Post subject:
Reply with quote

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
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne

PostPosted: Thu Aug 05, 2010 4:23 pm    Post subject:
Reply with quote

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
View user's profile Send private message
sendhilkumaran

New User


Joined: 15 Apr 2010
Posts: 8
Location: chennai

PostPosted: Thu Aug 05, 2010 4:51 pm    Post subject:
Reply with quote

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
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne

PostPosted: Thu Aug 05, 2010 5:13 pm    Post subject:
Reply with quote

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
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2 All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts find particular member name in PDS us... ravi243 CLIST & REXX 10 Mon Dec 19, 2016 6:44 pm
No new posts How to find the first monday of the w... abdulrafi COBOL Programming 10 Fri Nov 25, 2016 3:24 pm
No new posts Find out a active PGM jpsager JCL & VSAM 10 Fri Oct 07, 2016 4:32 pm
No new posts How to find a CICS resource used in C... Arunkumar Chandrasekaran CICS 8 Thu Sep 29, 2016 1:45 pm
No new posts Can we use OPTIMIZE FOR 1 ROWS for fe... cvijay784 DB2 1 Fri Aug 05, 2016 11:56 am


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us