"The order by clause in read statement is very limited (to specify a field and optionaly desc). We need more complex ordering using power of DBMS itself (e.g. Oracle). This could be similar to u_where vs. where clauses, where ""u_where"" is Uniface and ""where"" is DLMstatement passed ""as is"" to the DMBS."
There are many situations, where you need to sort occurrences with other criteria then just a field, but at the same time want to use power or hitlist. So one can't use sort statement, since it results in fetching all data. For example, one can order by: - maths function that DBMS supports (e.g. sums, products) - any other functions, be it analytics or aggregate - string manipulating functions (substrings, even with regexp) - combination of fields using operators (e.g. arithmetic and/or concatenation operator) - conversion functions (field is defined as string (varchar), but we need to read order by as numeric, since there are only numbers) - pseudo columns (in Oracle) - using extra params, e.g. NULLS FIRST/NULLS LAST
Nowadays, order by in Uniface is limited only to an existing field, but many times data needs to be sorted in more complex way. Fetching all data just to do sort in Uniface is very resources consuming (be it time and/or memory). DMBS order by clause seems to be powerful enought to do the task. Even in RIA (web) this would be very useful in pagination tasks (retrieving only several rows while respecting the order of data).