You may come across an unexpected lack of sort order in query results, despite sorting being defined in the corresponding UQL statement. This is a known limitation associated with most of these cases.
Due to an intrinsic limitation in the implementation of the UQL query engine of ACRM, it is not possible to enforce sort order on fields belonging to info areas other than the primary info area of the query.
For example, in a Join between info areas Additional Address (ZA) and Company (FI), where ZA is the primary info area, using FI fields to define a sort order has no effect.
The following UQL query illustrates the issue. The sort order (orderby) defined for field ZAFI.Company will have no effect:
select (ZAFI.Company, AddressType, CompanyName, Country, ZipCode) from (ZA)
plus (FI as ZAFI)
orderby (ZAFI.Company, AddressType)
From Version 11.10.0 onwards, a workaround exists for the limitation.
The workaround is possible due to the introduction of support for sorting on reference fields (Z-Fields).
The workaround consists of 2 steps:
- A custom field must be added to the primary info-area referencing the field of the secondary info area where the sort order is intended;
- The new field is then used to define the intended sort order in the UQL query
Note: Because of the use of reference fields, it can only be used when the secondary info area is the parent in the N-1 relationship with the secondary info area.
On the above example query, to sort on Company name a new field must be added, in the data mode, to the info area ZA providing a reference to the field Company of FI.
The following UQL query illustrates the changes in relation to the original query affected by the limitation:
select (F7000, AddressType, CompanyName, Country, ZipCode, ZAFI.Company) from (ZA)
plus (FI as ZAFI)
orderby (F7000, AddressType)
Please do not hesitate to contact Support if you have any questions.
With our best regards,