[Rt-devel] SearchBuilder and sorting in Oracle
SO at schilling.dk
Thu Feb 12 05:37:02 EST 2009
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
Tel: +45 70 27 99 00
Fax: +45 70 27 99 10
Mailto:so at schilling.dk<mailto:so at schilling.dk>
-------------- next part --------------
An HTML attachment was scrubbed...
More information about the Rt-devel