prodataset sort issues with temp-table defined LIKE db table

Posted by David Ysmael on 12-Feb-2018 16:38

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").

DATASET dstrkChkBE:FILL().

BUFFER tttrkChk:detach-DATA-SOURCE.

Posted by David Ysmael on 14-Feb-2018 10:19

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.

Posted by David Ysmael on 14-Feb-2018 15:56

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.

All Replies

Posted by Stefan Drissen on 12-Feb-2018 16:57

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.

Posted by David Ysmael on 14-Feb-2018 10:19

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.

Posted by David Ysmael on 14-Feb-2018 15:56

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.

This thread is closed