I’ve been struggling with CustomFields in RT not being shown in the desired order, and I have now pinpointed where the problem apparently is.
As I see it, it is Oracle’s query optimizer that prevents the DistinctQuery-SQL to return the rows in correct order. I have tweaked the generated SQL in DistinctQuery (Oracle.pm) and it now seems to give the correct result (at least my CustomFields are now shown in the right order :)). You may argue, that there is an overhead in my solution (two order bys), but I do not have enough in-depth knowledge of how the whole SearchBuilder works.
Anyway - you may adopt the solution or simply use it as inspiration
diff DBIx-SearchBuilder-1.54/SearchBuilder/Handle/Oracle.pm Oracle.pm
< $$statementref = “SELECT main.* FROM ( SELECT main.id FROM $$statementref $group $order ) distinctquery, $table main WHERE (main.id = distinctquery.id)”;
$$statementref = qq [
SELECT main.* FROM
( SELECT rt_internal_table.id, rownum rt_internal_sort_order FROM
( SELECT main.id FROM $$statementref $group $order ) rt_internal_table
) distinctquery, $table main
WHERE (main.id = distinctquery.id) order by distinctquery.rt_internal_sort_order
Configuration used: Perl v5.8.8, Oracle 10.2.0.1.0, RT3.8.1 and SearchBuilder 1.54