Denes Kubicek ApEx BLOG: Sorting on apex

Friday, 8 February 2008

Sorting on apex_item

If you use apex_item syntax in your query, you will not be able to sort on the columns in a way you would expect because ApEx will sort the column on the string you use as input. This means it will sort on something like

<input id="f04_9" type="text" style="text-align: right;" value="2300" maxlength="12" size="12" name="f04"/>

To overcome this problem you may want to use the following in front of your apex_item:

'<INPUT TYPE="HIDDEN" VALUE="'
|| LPAD (sal, 20, '0')
|| '" />'
|| apex_item.text (4,
TO_CHAR (sal),
12,
12,
'style="text-align:right" ',
'f04_' || ROWNUM,
NULL
) sal_editable

You could use any other value instead of the original one as well (ROWNUM, ename) and use it for sorting.

Here, you will find a working example:

http://htmldb.oracle.com/pls/otn/f?p=31517:167

Enjoy.

2 comments:

Patrick Wolf said...: Hi Denes,

an alternate solution would be to use the following query.

SELECT ...
apex_item.text (4, sal, 12, 12, 'style="text-align:right" ', 'f04_' || ROWNUM, NULL) AS sal_editable
, sal
FROM XXX

1) Set the "sal_editable" column to "Show Column=No"
2) Set the "HTML Expression" of "sal" to "#SAL_EDITABLE#"
3) Don't forget to set "Skip HTML=No" for the report.

That solution has the advantage that Oracle can do the ORDER BY with the original data type/column and can use an index on the column if one is available. I think it's also a better separation of query and UI logic.

Greetings
Patrick; 9 February 2008 at 20:43
Denes Kubicek said...: Patrick,

This is the same as:

http://htmldb.oracle.com/pls/otn/f?p=31517:153

However, you need to turn the Strip HTML off for that and you need to create an additional column as well. There are also other ways of achieving the same result.

Denes; 10 February 2008 at 10:35