Guru: RPG Sorting and Searching, A 7.2 Update

September 5, 2018 Susan Gantner

Author’s Note: This is the second update I’ve done to a tip I originally wrote back in 2008. In 2010, I updated the code to use SORTA with array data structures. Here in 2018 I’m updating it once more — this time to use free-form declarations in place of the D specs, including a more obvious way to code the nested data structure used in some of the examples and references to more recent tips for handling very large arrays. The 2010 version was entitled “. . . A 7.1 Update.” I’ve renamed this one as “. . . A 7.2 Update” since some of the coding techniques used require a later release.

The original tip was 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 that tip, but SORTA was not supported for them at that time, so I was forced to use the less-than-obvious group fields technique. With new support in RPG, things have changed, so I’m revisiting the example with an updated look and a few extra goodies.

The example program that I used before was very simple to retrofit to use 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 (now updated to free-form):

Dcl-Ds SflRecData ExtName('DSPPRODSF2':'PRODSFL':*Output) End-Ds; 
Dcl-Ds SflDS Inz;
  SflData Like(SflRecData)
       Dim(999);
  Name Like(ProdDS)
       Overlay(SflData);
  Price Like(SellPr)
       Overlay(SflData:*Next);
  Qty Like(STOH)
       Overlay(SflData:*Next);
End-Ds;            

To this simplified example, with no need for Overlay keywords to create the Group Field behavior:

Dcl-Ds SflRecData ExtName('DSPPRODSF2':'PRODSFL':*Output) End-Ds;
Dcl-Ds SflData LikeDS(SflRecData) Dim(999);       

The logic for the program is nearly identical to the original, except that because of the use of the array data structure, we are required to use qualified data names. Earlier logic that filled the array calculated the Count field referenced below. It represents the number of array elements that have been populated with data. So, to revisit, the logic for the sort 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 sort logic now changes as shown below. Note that the subfield names have changed because I am now able to use the original field names (qualified by the DS name) due to the use of LikeDS of the subfile record format. While the qualified names are longer, I think it makes the code easier to understand.

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, using the 7.2 support for inline nesting of data structures (instead of using the older LikeDS technique):

Dcl-Ds ProductData DIM(99) Qualified;
  CatCode Like(CatCod);
  ProdCount Like(Count);
  Dcl-DS ProdDetail DIM(999) Qualified;
    Name Like(ProdDS);
    Price Like(SellPr);
    Qty Like(STOH);
  End-Ds;
End-Ds;                               

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 this new support makes the use of SORTA more obvious than the old group field approach. Of course, 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 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. If you were to have a requirement for arrays bigger than the current 16 MB RPG limit, you could use RPG’s teraspace support, which requires different handling methods to the ones shown here. Links to IT Jungle tips by Jon Paris about doing these things in teraspace are included in the Related Stories section below.

Another reason I prefer this in-program array sorting technique is that it will often be considerably more efficient than using a database — re-retrieving the data on every request. 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 recent 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 an update on a tip from 2009 where I described the advantages of %LookUp for searching arrays. Now 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. Relatively small enhancements to things like SORTA and nested data structures are easy to miss when overshadowed by bigger features added in a release. These aren’t the only recent RPG enhancements. Stay tuned for future tips on other new features in RPG.

Susan Gantner, an IBM Champion and co-author of the popular Redbook Who Knew You Could Do That with RPG IV, is one of the top speakers/writers/trainers on IBM i development topics. She is a partner at Partner400 and System i Developer, and she hosts the RPG & DB2 Summit twice per year with partners Jon Paris and Paul Tuohy.

RELATED STORIES

Want a Fast and Easy Way To Sort Subfile Data?

A Bevy of BIFs: Look Up to %LookUp

Guru: Teraspace To The Rescue

Guru: Sorting In Teraspace

Guru: Searching Teraspace