I've created a REST service in my project and am using prodatasets and data-sources to populate the data before it is returned to the client sending the request. The issue I am having is with sorting the data in the dataset to be passed back to the client. I've defined a query on the data-source and am using a BY clause on that query I prepare, but it's not "fully" sorted by that filter. It seems that the index defined on the DB table (tt has been defined LIKE db table) is always applied. Here is the pertinent source code for all that I'm doing. Any help or advice is appreciated.
DEFINE TEMP-TABLE tttrkChk NO-UNDO LIKE trk-chk BEFORE-TABLE bttrkChk.
DEFINE DATASET dstrkChkBE FOR tttrkChk.
DEFINE QUERY trkChkQuery FOR trk-chk SCROLLING.
DEFINE DATA-SOURCE trkChkSource FOR QUERY trkChkQuery.
DATASET dstrkChkBE:EMPTY-DATASET ().
BUFFER tttrkChk:attach-DATA-SOURCE (DATA-SOURCE trkChkSource:handle).
QUERY trkChkQuery:QUERY-PREPARE ("FOR EACH trk-chk where Whs-code = '01' and check-in-date = '2018-02-12' BY check-in-time").
My solution was to NOT define the temp-tables LIKE a db table and instead just define all fields LIKE the db table's field and not define any indexes. Now the order by statement functions as expected and the behavior of the SAVE-ROW-CHANGES on the datasource has not been affected.
My solution was to not define the temp-table LIKE the db table and instead define each field individually LIKE the db's table field, without any indexes defined. The BY clause now behaves as expected and the SAVE-ROW-CHANGES on the temp-table attached to the data-source has not been affected.
Add the index you need for sorting to the define temp-table (documentation.progress.com/.../index.html
define temp-table tttrkChk no-undo like trk-chk before-table bttrkChk index ttix check-in-time.
Defined as above your temp-table will have only this index.