Mapping the ORDER BY clause: OrderByClauseMapper element

To map the ORDER BY clause, use the OrderByClauseMapper element in mappings.xml. The OrderByClauseMapper element has two attributes, UseSelectItemIndexes and PushComplexExprs.

UseSelectItemIndexes attribute

Most databases support expressions in the ORDER BY clause of a query, for example:

SELECT contact_last

FROM CUSTOMERS

ORDER BY city, contact_last || ', ' || contact_first

Some databases, however, do not. The SELECT clause must contain the expression and the ORDER BY clause must reference the expression by index, for example:

SELECT city, contact_last || ', ' || contact_first

FROM CUSTOMERS

ORDER BY 1, 2

If your database does not support expressions in the ORDER BY clause, set the UseSelectItemIndexes attribute to true, for example:

<OrderByClauseMapper UseSelectItemIndexes="true" />

PushComplexExprs attribute

Some databases do not support ORDER BY expressions other than column references. For such databases, an ORDER BY expression that is not a column reference should not be sent to the database. For example, ORDER BY contact_last should be sent to the database, but ORDER BY contact_last || ', ' || contact_first should not.

If your database does not support ORDER BY expressions other than column references, set the PushComplexExprs attribute to false, for example:

<OrderByClauseMapper PushComplexExprs="false" />

Copyright Actuate Corporation 2012