MVSFORUMS.com

harisubs · Beginner Joined: 03 Aug 2007 Posts: 8 Topics: 2

Hi
I have a query for the Pageup and Pagedown logic in IMS screen and I need to fetch records from DB2 table.

The normal pageup and pagedown logic will be based on the key fields displayed on the screen. For pageup, we use the first record displayed on the screen to fetch the previous set of records from DB2 table.

For pagedown, we use the last record displayed on the screen to fetch the next set of records from DB2 table.

The problem is:
I need to display the records on the screen sorted on a different set of fields other than the KEY field and this 'different set of fields' can contain the same values.
If I try to sort them on the 'different set of fields' while retrieving from DB2 itself then I lose track of the Key field as my key field gets jumbled and I would not be able to get the 'correct' next/previous set of records while performing pageup and pagedown.

I will try to list a sample data to add more clarity to the problem.

Data in DB2 table
============

kolusu · Posted: Mon Aug 13, 2007 9:40 am Post subject:

harisubs,

From what I see your screen data is sorted on Date and key_field.

Page up/normal cursor. for first time move low-values to ws-key-field and ws-date-field. When page up move the last key values from the screen to these 2 variables.

harisubs · Beginner Joined: 03 Aug 2007 Posts: 8 Topics: 2

Kolusu
The data on screen is sorted on Date,Amount and Key-fld respectively.
I have a query for the solution provided.

Considering the same data as given above (first post) and let us assume that I am displaying 2 records at a time on the screen. Then if user performs Page up operation then at the 3rd page the user would have the following data on screen

KEY_FLD-------DATE---------------AMOUNT
57------------2001-08-01--------130.00
58------------2001-08-01--------140.00

And if the user performs a pageup operation again then the query will look like

SELECT *
FROM TABLE
WHERE KEY_FIELD > 58
AND DATE_FIELD > 2001-08-01
ORDER BY DATE_FIELD
,KEY_FELD

This query would not fetch the last record as the kEY_FLD's values is 52 which is less than 58. Ideally, it should fetch the below record on 4th page.

52------------2001-08-27--------100.00

Also, as I had mentioned there could be duplicate values on the date and amount field.
So, if we just use '>' for pageup and '<' for pagedown (in the query) then we will be losing data.

kolusu · Posted: Tue Aug 14, 2007 7:16 am Post subject:

harisubs,

what is the format/definition of all the columns involved here?

Kolusu
_________________
Kolusu
www.linkedin.com/in/kolusu

harisubs · Beginner Joined: 03 Aug 2007 Posts: 8 Topics: 2

Kolusu
The KEY-FLD is numeric, DATE field would be converted to MMCCYY format (example: 082007) while displaying on screen and AMOUNT field is numeric.
The sorting should be done on DATE,AMOUNT,KEY-FLD respectively

Thanks
Harisubs

kolusu · Posted: Tue Aug 14, 2007 8:25 am Post subject:

harisubs,

Try this. You need to concatenate all the 3 fields together and treat it as a key. Move the last entry of the screen to the level 10 fields and use that information to get the next key

harisubs · Beginner Joined: 03 Aug 2007 Posts: 8 Topics: 2

Thank you Kolusu.
I made a short test in SPUFI and the results are as desired.
Shall get back if I face any trouble

Thanks
Harisubs