ORDER BY<order by condition>
The ORDER BY clause has the following syntax:
ORDER BY <order by condition> [ WITH RETRY [REPEAT LAST ROW] ]
This clause determines the order of the records in the output table. In the <order by condition>, you list one or more fields from the <select list> in the SELECT clause. The field listed first in the ORDER BY clause takes precedence. You can list the fields by name or you can specify an index to their position in the SELECT clause. For each field in the <order by condition>, you can indicate if the order is to be ascending (default) or descending – for example, ORDER BY 1 asc, 2 desc.
If retry points are included, you must use ORDER BY to ensure that the sequence after return to the retry point is identical to the one used the first time. After a jump to a retry point, it is important that the query continues at its position in the selected set at the time that the retry occurred. By using the WITH RETRY addition this is easy to realize. By performing a commit.transaction(), the values for the retry conditions are saved. In case of a jump to a retry point, the query continues, starting from the values saved during the commit.transaction(). In the case of WITH RETRY the new set starts after the saved values. In the case of WITH RETRY REPEAT LAST ROW, the set starts at the saved value; so the last record is read again. In the case of WITH RETRY REPEAT LAST ROW, enter a commit.transaction() in the SELECTEOS part of the select statement to prevent a new build-up of the query in some cases.
Example (embedded SQL)
SELECT tisfc001.*
FROM tisfc001 FOR UPDATE
WHERE tisfc001.pdno < 100
ORDER BY tisfc001.pdno WITH RETRY
SELECTDO
tisfc001.proc = tcyesno.yes
db.update(tisfc001, DB.RETRY)
commit.transaction()
ENDSELECT
| In this case we do not use REPEAT LAST ROW, because the
| moment retry takes place, the value present at the stage of
| commit.transaction() has certainly been saved.
Related topics
|
