Subfile Relative Record Number in RPGLE

Or do you want to delete a record from one position and insert it in a different position (thereby shifting other records too)?
yes.

I am looking to move a record (if that records' store number is the default store of the user displaying the subfile) to position number 1, and readjust all other records by +1. (And removing the empty place in the subfile).
I actually do have this crudely accomplished by reading subfile(A) by RRN and finding the record with the correct store number, changing it's RRN and writing it to subfile(B), along with all other subsequent records from subfile(A) with an RRN of RRN + 1.  

Initially, it looks like I can and do change the RRN of subfile(A).  But, upon displaying subfile(A) the record order  remained in the same.  Which could only mean I didn't actually change the RRN.   So, I went with creating subfile(B) from subfile(A).

Which leads me to ask the question if this is solution is the only good one?  This subfile contains records that takes hundreds of lines of code written years ago to build inventory variances, balances and adjustments.  I am looking to stay away from modifying any kind of logic.  This would be a quick win for the user community to display the records with their store number at the top of the subfile, so that they don't have to page to it 50 times a day..




 

any thoughts are greatly appreciated!!!!!

BoomerSooner013:

I suspect that there is no easy method, that your A+B subfiles is the appropriate one.

With that said...

First questions should be about the underlying files. (I assume they're PF/LF files rather than SQL TABLEs, VIEWs, INDEXs.) What file sizes are involved?

How many stores, for example? How many subfile rows? Can you collect basic info on I/O volume when the program runs normally?

At what point do you know what StoreNbr is the "current" one?

My first thought is to base the order on a SQL SELECT that generates an extra column for sorting:

 [ CASE WHEN StoreNbr = :currentstore THEN '0' ELSE '1' END AS StoreSort ]

By using a generated column to assist with initial program input, you might be better able to sort by having that column as highest-order ORDER BY column.

If a change can be "inserted" into the right point in program logic, it might be unnecessary to make extensive changes. If files had the "current" store records at the beginning, sorting later wouldn't be relevant.

Tom

This sounds great...
except that the underlying files are huge.  However, the ending subfile(A) is only 54 records long.  The subfile contains one record per store.
I did do some research today and found an API called:
QLGSRTIO

This one promises to be able to accept a subfile, and then be able to rewrite it.  I am trying to get it to work right now.  This API requires a read of the entire subfile(my case = 54 records) and write each record to the API, (manually) clear the subfile, and then 'read' the API and write the records again to the subfile.
This sounds like what I want to do.  As I initially read the subfile I can make things happen the way I wish...select the right record make it RRN 1...etc...write the record to the API and then retrieve.
It sounds great.  Accept, now I am only getting the last record in the API 54 times in my subfile.  I haven't made any changes to the subfile yet.  As I just want the API  to work....

Have you ever worked with this API?

I have a work around developed.  I can make this whole situation go away by writing subfile(A) to subfile(B).  But, I am on a mission to make this work......

BoomerSooner013:

> [QLGSRTIO] Have you ever worked with this API?

Yes, but it's been quite a few years. I'd probably go with qsort() nowadays.

But SORTA in RPG might be easier for you. Hmmm... RPG IV? or RPG III? (I.e., are you compiling as ILE or OPM?) RPG IV with the %subarr() function to limit just to the first 54, or however many, elements should be easy.

Tom

ok...
In order to use QSORT() or SUBARR() I would have to put the subfile into an array and then execute those commands...right?  Then write those records back into the subfile?

With the API, it looks like I can read the subfile and check for my conditions, change the rrn number as(if) necessary write it to the API and then read the records back out of the API in RRN order.  So far, this has given me success.  and I don't have to have an array OR an additional subfile.  I am still testing this, as I managed to get the API to work, late last evening.

We are RPG IV and creating ILE objects with in house compile command(To grab a large service pgm).

is using QLGSRTIO API and old hat kind of thing to do?

BoomerSooner013:

> So far, this has given me success.

If that means that you have been successfully using the QLGSRTIO API (and, of course, the necessary additional QLGSORT API which creates the sort environment for QLGSRTIO) in this program, then it probably should continue to be used.

However, if 'success' only means that you've read subfile A and (re)written to subfile B and you hope to insert the API(s) between those two procedures, then I have to recommend creating and sorting an array. There will be far less executable code and far fewer data definitions being added.

The proper definition of the Request Control Blocks alone, for the TWO APIs, will be more than you'll want to do. Particularly for QLGSORT, the RCB is tricky and difficult to master even just for the data definitions.

QLGSORT and QLGSRTIO are useful for 'National Language Support' in particular cases. They can also be coaxed into doing a couple things that are extremely difficult under certain conditions. Finally, they are almost necessary in early versions of the OS.

But especially after OS/400 V5R1 went GA, and later, other sorts have made general basic sorting easy. If you are adding a sort capability and you don't need to support multiple languages and multiple CCSIDs and you aren't distributing software products to other countries and you aren't supporting different early releases and one or two other possible conditions, keep it simple.

I have to recommend defining a simple array, reading into the array, running a single SORTA instruction, then writing out from the sorted array in order.

Simple, straightforward, self-contained subroutine (or proc), obvious in intent.

Tom

Thank you very much for your advice.  

I am inclined to go with you on the process of reading the subfile into an array and then reading those records back out of the array.  This is very straightforward, and still less code than modifying the DDS with another subfile bucket, only needed temporarily.

I don't write too many arrays.  Is there a sample somewhere of this kind of array usage?

Thanks again Tom....your time and advice has been extremely helpful...
Tina

Tina:

Can you copy/paste the subfile record from a compile listing here? I can probably take the definitions and supply D-specs here for a DS that would sort easily with SORTA.

Simple IBM examples:

http://publib.boulder.ibm.com/infocenter/iseries/v5r4/index.jsp?topic=/books_web/sc092451743.htm
http://publib.boulder.ibm.com/infocenter/iseries/v5r4/index.jsp?topic=/books_web/sc092451203.htm

Tom

sure...
I apologize for the delay in getting back to you...
The subfile that we are working on is
RTLSFL

Tina:

Which field is the "store number"? LOC#A?

Tom

Thank your for your advice...it was very useful, and helped me solve my issue....