Is it possible to sort data in a temp-table after it has been loaded into a temp-table via a call to FILL()? In this example, our client (a mobile device) is expecting to receive sorted data from the server. The issue is that the field we should be sorting by does not exist in the database, and thus, can not be used in the BY-clause for the associated query that initially loads the temp-table. We are using an AFTER-ROW-FILL callback to populate data values into the non-db field of the temp-table. Once FILL() is complete, we want to provide the sorted data to the client so that it does not have to do this action.
One suggestion was to take the loaded temp-table and use it as a data-source to load another temp-table via a Query, that can reference the (now populated) non-db field, with a BY clause in the predicate statement.
Is there another way to accomplish this task? I readily admit I feel like I am missing something obvious (with regard to this issue) and would welcome any constructive advice. Specifics are below.
We have the following temp-table definition:
DEFINE TEMP-TABLE ttOrder NO-UNDO BEFORE-TABLE before-ttOrder FIELD orderStatus AS CHARACTER /* maps to a db field */ FIELD createDate AS DATE /* maps to a db field */ FIELD completedDate AS DATE /* maps to a db field */ FIELD relevantDate AS CHARACTER. /* non-db field; this is the field to sort by */ We have also defined a callback: /* the callback method is used to set relevantDate values */ hdlBuffer:SET-CALLBACK ("AFTER-ROW-FILL", "SetRowCallback", THIS-OBJECT). The callback method definition: METHOD PRIVATE VOID SetRowCallback ( INPUT DATASET-HANDLE dsHdl ): DEF VAR hEntityBuffer AS HANDLE. /* determine what the value for field relevantDate should be based on the Order's status */ hEntityBuffer = dsHdl:GET-BUFFER-HANDLE (1). IF VALID-HANDLE(hEntityBuffer) THEN DO: IF hEntityBuffer:BUFFER-FIELD('orderStatus'):BUFFER-VALUE = 'Complete' THEN hEntityBuffer:BUFFER-FIELD('relevantDate'):BUFFER-VALUE = hEntityBuffer:BUFFER-FIELD('completedDate'). ELSE hEntityBuffer:BUFFER-FIELD('relevantDate'):BUFFER-VALUE = hEntityBuffer:BUFFER-FIELD('createDate'). END. END METHOD.
/* now...sort the temp-table by the values stored in field relevantDate */
????
DEFINE TEMP-TABLE ttOrder NO-UNDO BEFORE-TABLE before-ttOrder
/* fields */
INDEX idx1 relevantDate.
Is it possible to sort data in a temp-table after it has been loaded into a temp-table via a call to FILL()? In this example, our client (a mobile device) is expecting to receive sorted data from the server. The issue is that the field we should be sorting by does not exist in the database, and thus, can not be used in the BY-clause for the associated query that initially loads the temp-table. We are using an AFTER-ROW-FILL callback to populate data values into the non-db field of the temp-table. Once FILL() is complete, we want to provide the sorted data to the client so that it does not have to do this action.
One suggestion was to take the loaded temp-table and use it as a data-source to load another temp-table via a Query, that can reference the (now populated) non-db field, with a BY clause in the predicate statement.
Is there another way to accomplish this task? I readily admit I feel like I am missing something obvious (with regard to this issue) and would welcome any constructive advice. Specifics are below.
We have the following temp-table definition:
DEFINE TEMP-TABLE ttOrder NO-UNDO BEFORE-TABLE before-ttOrder
FIELD orderStatus AS CHARACTER /* maps to a db field */
FIELD createDate AS DATE /* maps to a db field */
FIELD completedDate AS DATE /* maps to a db field */
FIELD relevantDate AS CHARACTER. /* non-db field; this is the field to sort by */
We have also defined a callback:
/* the callback method is used to set relevantDate values */
hdlBuffer:SET-CALLBACK ("AFTER-ROW-FILL", "SetRowCallback", THIS-OBJECT).
The callback method definition:
METHOD PRIVATE VOID SetRowCallback ( INPUT DATASET-HANDLE dsHdl ):
DEF VAR hEntityBuffer AS HANDLE.
/* determine what the value for field relevantDate should be based on the Order's status */
hEntityBuffer = dsHdl:GET-BUFFER-HANDLE (1).
IF VALID-HANDLE(hEntityBuffer) THEN DO:
IF hEntityBuffer:BUFFER-FIELD('orderStatus'):BUFFER-VALUE = 'Complete' THEN
hEntityBuffer:BUFFER-FIELD('relevantDate'):BUFFER-VALUE = hEntityBuffer:BUFFER-FIELD('completedDate').
ELSE
hEntityBuffer:BUFFER-FIELD('relevantDate'):BUFFER-VALUE = hEntityBuffer:BUFFER-FIELD('createDate').
END.
END METHOD.
/* now...sort the temp-table by the values stored in field relevantDate */
????
Flag this post as spam/abuse.
Peter, thank you for the answer. Very much appreciated, sir!