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.




on

2 comments:

  1. Patrick Wolf9 February 2008 at 20:43

    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

    ReplyDelete
  2. Denes Kubicek10 February 2008 at 10:35

    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

    ReplyDelete