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:
In order to implement the sorting in RPG program:
****************************************************************
* **
* 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
D apiNameToField S 70A DIM(999) CTDATA PERRCD(1)
** Parameter name in API method Field name in file
parameter1 FLD01
parameter2 FLD02
...
D lOrderBy S 1024A VARYING
lOrderBy = ctlGetOrderBy();
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;
...
{"method":"exampleMethodForItems.get","parameters":{},"control":{"orderBy":"parameter2 DESC, parameter1"}}
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.
orderBy input parameter value is not correct. Records are in default order in response.
Then rows sent in response are in default order.
There are examples in QSAMPLES source file:
Also the following skeleton programs where lines are marked by:
*-SORTING-START-
*-SORTING-END-
are available in QSAMPLES source file: