SELECT ( NVL(SUBSTR(LAST_NAME,1,25), ' ') || ', ' || NVL(SUBSTR(FIRST_NAME, 1,12), ' ') || ' ' || NVL(SUBSTR(MIDDLE_NAME, 1, 10), ' ') ) As AGNT_NAME
ORDER BY LAST_NAME, FIRST_NAME, MIDDLE_NAME
Without the DISTINCT I have no problems.
The way I was thinking about the problem was that, if any of these fields ever has an index associated with it in the future (none do currently), then be leaving them in their original format in the ORDER BY might influence the optimizer's approach to use that index when running the query.
Cause: There is an incorrect ORDER BY item. The query is a SELECT DISTINCT query with an ORDER BY clause. In this context, all ORDER BY items must be constants, SELECT list expressions, or expressions whose operands are constants or SELECT list expressions.
Action: Remove the inappropriate ORDER BY item from the SELECT list and retry the statement.