PROCEDURE GetPageResult(nPageSize, lFirst, lLast, nOffset, cOffSet, cWhere, cOrderBy) >LOCAL cOrderBy2, cOrderBy3, cPageSize, cTop > >* nPageSize = Size of the pages, in your example 50 >* lFirst, Show first page >* lLast, Show last page >* nOffSet, Show from record nOffset onwards >* cOffset, drilldown to an alphabetic character selected (could be one or more, so A,B,C or AA, AB, AC etc) > >* cWhere: It is assumed that the selection criteria: cWhere is constructed by an upper layer. So for example > * WHERE State = 'State of California' AND status IN(2,3) > * WHERE Address LIKE '%Lane%' AND State is 'NY' AND status = 1 > >* cOrderBy Indicates the fields on which the resultsets need to be ordered. This could be a comma delimited list > * in this case the selection of offset would only operate on the first column in the list > >* It assumed that the current page location is handled by the application layer (stateless) and the page number display is assumed >* to be handled by the upper layer. This routine is only retrieving the requested pages from the backend. > > >* Getting the 50 located records. > >cOrderBy2 = cOrderBy >cOrderBy3 = cOrderBy >cPageSize = STR(nPageSize) >cTop = cPageSize > >** Handling the naviation to a particular starting char > >cWhere = cWhere + IIF(EMPTY(cOffSet,'',' AND '+GETWORDNUM(cOrderBY,1) + " LIKE '"+ALLTRIM(cOffSet))+"%'" > >DO CASE > CASE lLast > cOrderBy2 = cOrderBy+' DESC' > > CASE nOffSet > 0 > cTop = STR(nOffset+nPageSize) > cOrderBy3 = cOrderBy3+' DESC' >ENDCASE > >** SQL Statement will return at most (nPageSize records) with the total rowcount of the selection in the Totalrows column > >TEXT TO cSQL TEXTMERGE NOSHOW > SELECT TOP <<cPageSize>> * FROM ( > SELECT TOP <<cPageSize>> * FROM ( > SELECT TOP <<cTop>> * > FROM Customers > WHERE <<cWhere>> > ORDER BY <<cOrderBy2>> ) as A > ORDER BY <<OrderBy3>>) as B > INNER JOIN (SELECT COUNT(*) as totalrows FROM Customers WHERE <<cWhere>>) as C ON 1 = 1 > ORDER BY <<cOrderby>> >ENDTEXT > >=SQLEXEC(Applic.Sqlhandle, cSql, 'QueryResult') >RETURN