How to implement sorting?

Sorting feature is an option in methods which return array as response and are SQLRPGLE program with embedded SQL. It gives the possibility of arranging items in a sequence ordered by some criterion.

The sorting in Aperio is based on orderBy control parameter where the passed value can be as follows 'parameter1 [ASC | DESC], parameter2 [ASC | DESC], ...'

In this syntax:

parameter1, parameter2, etc., that can be names of method parameters by which you want to sort the result set
ASC to sort the result set in ascending order (from low to high) and DESC to sort the result set in descending order (from high to low). The ASC or DESC is optional. If you skip it, ASC is used by default.

In order to implement the sorting in RPG program:

Add the following procedure:

      ****************************************************************
      *                                                             **
      * Subprocedure: lcl_processOrderBy                            **
      *                                                             **
      ****************************************************************
      *
     P lcl_processOrderBy...
     P                 B
      *
     D                 PI          1024    varying
     D pApiNameToFld                 70    DIM(999) const
     D pInOrderBy                  1024    varying
      *
     D n               S              9  0
     D i               S              9  0
     D pos             S              2  0
     D lOutOrderBy     S           1024    varying
     D lArray_ptr      S               *
     D lDelimiter      S             30    inz(*all',')
     D lOrderByElem    S             61
     D lRemider        S                   like(lOrderByElem)
     D lAPIName        S             60
     D lFieldName      S             10
     D lMSGDATA        S            360
     D                 DS
     D names_DS                1     70
     D  name                   1     60
     D  field                 61     70
     D x               S              5  0
     D lAPINames       S           1024    varying

      /FREE

       //Convert search text to array of words
       lArray_ptr = *null;
       if pInOrderBy <> *blanks;

         lArray_ptr = stringToArray(%ADDR(pInOrderBy) + 2:
           %LEN(pInOrderBy):
           lDelimiter);

         //Get number of records form array of strings (words)
         if lArray_ptr <> *null;
           n = arrayGetNumOfElements(lArray_ptr);
         endif;

         for i = 1 by 1 to n;

           lOrderByElem = %triml(arrayGet(lArray_ptr:i));
           pos = %scan(' ':lOrderByElem);

           if pos > 1;

             // Get API field name
             lAPIName = %subst(lOrderByElem:1:pos - 1);
             lRemider = %subst(lOrderByElem:pos);

             // Get field name
             exsr srGetFieldName;

             if lFieldName <> *blanks;

               lOrderByElem = %scanrpl(%trim(lAPIName): %trim(lFieldName):
                 lOrderByElem:1:pos - 1);
               lOutOrderBy += ', ' + %trim(lOrderByElem);

             else;

               lMSGDATA = lAPIName;
               exsr srGetAPINames;
               %subst(lMSGDATA:61) = lAPINames;
               // In non DC1 environment instead of IBSJob_raiseWarning() use:
               // sendPgmMsg(Z1PGMN:'M000063':'ORDERBY':lMSGDATA:%size(lMSGDATA)); 
               IBSJob_raiseWarning(ASWJob_newException('ORDERBY':'UI00729':
                 %size(lMSGDATA):lMSGDATA));

             endif;

             // Only ASC/DESC keywords or blanks after API name in reminder
             // are correct

             EXEC SQL
               VALUES(trim(upper(:lRemider))) INTO :lRemider;

             if not (lRemider = 'ASC' or lRemider = 'DESC'
               or lRemider = *blanks);

               clear lOutOrderBy;
               // In non DC1 environment instead of IBSJob_raiseWarning() use:
               // sendPgmMsg(Z1PGMN :'M000062':'ORDERBY');
               IBSJob_raiseWarning(ASWJob_newException('ORDERBY':'UI00728'));
               leave;

             endif;

           else;

             clear lOutOrderBy;
             // In non DC1 environment instead of IBSJob_raiseWarning() use:
             // sendPgmMsg(Z1PGMN :'M000062':'ORDERBY');
             IBSJob_raiseWarning(ASWJob_newException('ORDERBY':'UI00728'));
             leave;

           endif;

         endfor;

       endif;

       // Destroy array of strings (words)
       if lArray_ptr <> *null;
         arrayDestroy(lArray_ptr);
       endif;

       // Remove leading comma
       lOutOrderBy = %triml(lOutOrderBy:', ');

       return lOutOrderBy;

      ****************************************************************
      *                                                             **
      * srGetFieldName - to get file field name from based on API   **
      *                  field name                                 **
      *                                                             **
      ****************************************************************
       begsr srGetFieldName;

         clear lFieldName;

         //Field name in API method to field name in file
         x = 1;
         dow x < 999;

           if pApiNameToFld(x) = *blanks;
             leave;
           endif;

           names_DS = pApiNameToFld(x);

           if name = lAPIName;
             lFieldName = field;
             leave;
           endif;

           x += 1;

         enddo;

       endsr;

      ****************************************************************
      *                                                             **
      * srGetAPINames - to get the list of API fields names         **
      *                                                             **
      ****************************************************************
       begsr srGetAPINames;

         clear lAPINames;

         //Field name in API method to field name in file
         x = 1;
         dow x < 999;

           if pApiNameToFld(x) = *blanks;
             leave;
           endif;

           names_DS = pApiNameToFld(x);
           lAPINames += ', ' +  %trim(name);

           x += 1;

         enddo;

         lAPINames = %triml(lAPINames:', ');

       endsr;

      /END-FREE
      *
     P                 E

Add compile-time array to map method parameter name to field name in the file. The array should contain all parameters eligible to be used in sorting feature:

     D apiNameToField  S             70A   DIM(999) CTDATA PERRCD(1)

** Parameter name in API method                             Field name in file
parameter1                                                  FLD01
parameter2                                                  FLD02
...

Receive the control parameter for sorting using ctlGetOrderBy() procedure:

     D lOrderBy        S           1024A   VARYING

       lOrderBy = ctlGetOrderBy();

Use lcl_processOrderBy procedure to build ORDER BY clause for SQL statement which will be used to prepare SQL cursor to get the result set. You should pass compile-time array declared in 2. step (apiNameToField) and the variable from 3. step (lOrderBy) to lcl_processOrderBy:

       lSQL = 'SELECT FLD01, FLD02 FROM FILE01';

       lOrderBy = lcl_processOrderBy(apiNameToField:lOrderBy);
       if lOrderBy = *blanks;
         // Default sorting order if nothing has been passed via orderBy
         // control parameter
         lSQL += ' ORDER BY FLD01';
       else;
         lSQL += ' ORDER BY ' + lOrderBy;
       endif;  

       EXEC SQL
         PREPARE stmt FROM :lSQL;

       EXEC SQL
         DECLARE c CURSOR FOR stmt;

       EXEC SQL
         OPEN c;  
       ...

Then the request made from Aperio API Tool can look as follows:

{"method":"exampleMethodForItems.get","parameters":{},"control":{"orderBy":"parameter2 DESC, parameter1"}}

Warnings

If in the list of parameters passed via orderBy control parameter there will be a field which is not supported, UI00729 (M000063 in non DC1 environment) warning message is sent:

The response cannot be ordered by field <field_name> <HELP>Only following fields are supported: <list_of_supported_fields>. Field <field_name> is ignored.

Then rows sent in response are sorted only by the recognized fields.

If the value passed via orderBy control parameter doesn’t follow expected pattern, UI00728 (M000062 in non DC1 environment) warning message is sent:

orderBy input parameter value is not correct. Records are in default order in response.

Then rows sent in response are in default order.

Examples

There are examples in QSAMPLES source file:

EXAMPLE021 SQLRPGLE DC1 example. orderBy.get
EXAMPLE022 SQLRPGLE NON DC1 example. orderBy.get

Also the following skeleton programs where lines are marked by:

*-SORTING-START-
*-SORTING-END-

are available in QSAMPLES source file:

SKELETON08 SQLRPGLE DC1 skeleton of program with sorting
SKELETON09 SQLRPGLE NON DC1 skeleton of program with sorting