RPG Sorting and Searching: A 7.1 Update
September 29, 2010 Susan Gantner
Back in 2008, I wrote a tip about using SORTA and group fields in RPG to easily sort array data, such as subfile data. Array Data Structures existed back when I wrote the original tip, but SORTA was not supported at that time. So I was forced to use the less-than-obvious group fields technique described in that tip. With new support in RPG 7.1, things have changed, so I’m revisiting the example with an updated look.
The example program that I used before was very simple to retrofit to utilize an actual Array Data Structure. The definition of the array itself is much simpler since I can use LIKEDS to define the Data Structure and put the DIM keyword directly on it. So I went from this definition in the original example:
D SflRecData E Ds ExtName(DspProdSf2:ProdSfl:*Output) D SflDS Ds Inz D SflData Like(SflRecData) D Dim(999) D Name Like(ProdDS) D Overlay(SflData) D Price Like(SellPr) D Overlay(SflData:*Next) D Qty Like(STOH) D Overlay(SflData:*Next)
To this simplified example, with no need for Overlay keywords to create the Group Field behavior:
D SflRecData E Ds ExtName(DspProdSf2:ProdSfl:*Output) D SflData DS LikeDS(SflRecData) D Dim(999)
The logic for the program is nearly identical to the original, except that because the use of the Array Data Structure, we are required to use qualified data names. The Count field referenced below was populated in the logic where the data for the array (destined ultimately for the subfile) was populated. It represents the number of array elements that have been populated with data. So, to revisit, the logic for the sorting originally looked like this:
If SortByName; SortA %SubArr(Name:1:Count); ElseIf SortByQty; SortA %SubArr(Qty:1:Count); ElseIf SortByPrice; SortA %SubArr(Price:1:Count); EndIf;
The logic now changes to the next piece of code seen below. Note that the subfield names have changed because I’m now defining the subfields via LikeDS using the subfile record format.
If SortByName; SortA %SubArr(SflData(*).ProdDS:1:Count); ElseIf SortByQty; SortA %SubArr(SflData(*).STOH:1:Count); ElseIf SortByPrice; SortA %SubArr(SflData(*).SellPR:1:Count); EndIf;
In order to indicate which level of the DS we want to sort, the syntax for sorting an Array DS requires that we use an asterisk (*) instead of the normal index value. The * indicates all occurrences of the array at that level. This may not seem necessary in this example, but in a case where there are nested arrays in use, it is needed to indicate which of the arrays is to be sorted.
For example, suppose I had a DS that contained data similar to the three subfields above but there is another level of data–a Category code–above the product. In other words, I have data for up to 99 product categories, each of which has up to 999 products. So the definition would look like this:
D ProductDetail DS Template D Name Like(ProdDS) D Price Like(SellPr) D Qty Like(STOH) D ProductData DS DIM(99) Qualified D CatCode Like(CatCod) D ProdCount Like(Count) D ProdDetail LikeDS(ProductDetail) DIM(999)
My logic to sort this would need to specify whether I wanted to sort the products within a given category:
SortA %Subarr(ProductData(i).ProdDetail(*).Name : 1 : ProductData(i).ProdCount);
Or sort the Categories themselves:
SortA %SubArr(ProductData(*).CatCode : 1 : CatCount);
In practice, I would probably want to sort the categories and the product data within each category (i.e., product name within category code). This is the code to do that:
SortA %SubArr(ProductData(*).CatCode : 1 : CatCount); For i = 1 to CatCount; SortA %SubArr(ProductData(i).ProdDetail(*).Name : 1 : ProductData(i).ProdCount); EndFor;
I think the 7.1 support makes the use of SORTA more obvious than the old group field approach. Of course, some of you may be wondering why I chose to sort the data using an array in my RPG program at all? Why not, for example, gather the data for the array (destined for a subfile or whatever other use I may have for it) and write to a temporary work file, sequencing it as necessary using either logical views or SQL with Order by?
Personally I like the approach of keeping the data in my program and simply sorting it there. Often the work-file-based techniques we have used over the years came about because of memory constraints of midrange systems of old and/or the limitations of array handling facilities in RPG. I have gained a new appreciation for the power of arrays during my recent forays into the world of PHP and look to apply those lessons to my RPG work. The final inhibitors to using such techniques in RPG were removed when V6.1 removed the old 32K array size limits. Now that RPG can handle almost any array that I care to throw at it with such ease, I prefer the simplicity of this approach. Another reason I prefer it is that it will be considerably more efficient than using a database. Database is often overkill for the task, considering the overhead of opening, closing, record locking, and either creating or clearing the work file each time.
There is one more 7.1 enhancement to SORTA–the ability to specify an extender for either ascending (A) or descending (D) sequence. This makes is far easier to handle a requirement where the same data may need to be sorted in either ascending or descending sequence based on runtime requirements. As before, ascending sequence is assumed if neither is specified.
Before I close this tip, I’ll also offer a 7.1 update on a a tip from last year where I described the advantages of %LookUp for searching arrays. In 7.1, you can also search Array Data Structures, using a very similar syntax to the SortA example. If I wanted to search for a specific product name in the nested array structure above, I could do that with a statement like this:
Location = %LookUp(SearchValue : ProductData(i).ProdDetail(*).Name : 1 : ProductData(i).ProdCount);
RPG just keeps getting better. With all the hoopla over RPG OA in the 7.1 announcement, some of these less dramatic features may have escaped your notice. These aren’t the only recent RPG enhancements. Stay tuned for future tips on other 7.1 features in RPG.
Susan Gantner is half of Partner400, a consulting company focused on education on modern programming and database techniques and tools on the IBM i platform. She is also a founding partner in System i Developer, a consortium of System i educators and hosts of the RPG & DB2 Summit conferences. Susan was a programmer for corporations in Atlanta, Georgia, before joining IBM. During her IBM career, she worked in both the Rochester and Toronto labs, providing technical support and education for application developers. Susan left IBM in 1999 to devote more time to teaching and consulting. Together with Jon Paris, she now runs Partner400, and appears regularly at many technical conferences, including System i Developer’s RPG & DB2 Summit. Send your questions or comments for Susan to Ted Holt via the IT Jungle Contact page.