4 Replies Latest reply: Jun 26, 2013 10:30 AM by Madan N RSS

    Sorting a column as a numeric value

    Madan N

      Hello,

       

             I have created a SQL Report using collections and having problems with sorting one of the column as a numeric value.

      I have used create_collection_from_query2 to assign my numeric value as n001 variable. But, when i use the same in my SQL Report, it is considering the column as string.

      Please advise if i am doing anything incorrect which is causing this issue.

       

      Thank you in advance,

      Madan

        • 1. Re: Sorting a column as a numeric value
          Madan N

          An update to my above question:

                I have used to_number() function in the select statement. But, i get an error "Invalid Number" error. I have checked the underlying data and confirmed that the data is a numeric value with a 2 point decimal value.

          Just an FYI., i was using to_number() on  apex_item.display_and_save function.

          Any help is appreciated...

          • 2. Re: Sorting a column as a numeric value
            fac586

            MadanN wrote:

             

            An update to my above question:

                  I have used to_number() function in the select statement. But, i get an error "Invalid Number" error. I have checked the underlying data and confirmed that the data is a numeric value with a 2 point decimal value.

            Just an FYI., i was using to_number() on  apex_item.display_and_save function.

            A function that clearly returns a VARCHAR2 result:

            APEX_ITEM.DISPLAY_AND_SAVE(
               p_idx IN NUMBER,
               p_value IN VARCHAR2 DEFAULT NULL,
               p_item_id IN VARCHAR2 DEFAULT NULL,
               p_item_label IN VARCHAR2 DEFAULT NULL)
               RETURN VARCHAR2;
            
            SQL> with t as (
               2        select trunc(dbms_random.value(0, 100)) n from dual connect by level <= 5)
               3  select
               4      n
               5    , apex_item.display_and_save(1, n) d
               6  from
               7      t
               8  order by
               9      n;
            
                      N D
             ---------- ----------------------------------------------------------------
                      0 <input type="hidden" name="f01" value="0" /><span>0</span>
                      6 <input type="hidden" name="f01" value="6" /><span>6</span>
                     27 <input type="hidden" name="f01" value="27" /><span>27</span>
                     68 <input type="hidden" name="f01" value="68" /><span>68</span> 
                     74 <input type="hidden" name="f01" value="74" /><span>68</span> 
            

            Making attempts to convert such results to a number using to_number clearly doomed to failure:

            SQL> with t as (
               2        select trunc(dbms_random.value(0, 100)) n from dual connect by level <= 5)
               3  select
               4      n
               5    , to_number(apex_item.display_and_save(1, n)) d
               6  from
               7      t
               8  order by
               9      n;
            

             

               , to_number(apex_item.display_and_save(1, n)) d                          * ERROR at line 5: ORA-01722: invalid number

            To make the column properly sortable in a SQL report:


            1. Include the original number column in the query.
            2. Reference the apex_item.display_and_save column name in the number column's HTML Expression property using column reference syntax (#COLUMN_ALIAS#).
            3. Make the apex_item.display_and_save column hidden.
            • 3. Re: Sorting a column as a numeric value
              Madan N

              Thank you for the reply fac586.

              Making above changes will have the column hidden. But, my requirement was to allow the user to be able to sort the column.

               

              I have also re-created the same page using Interactive report. Is there an easy fix to this issue using IR?

              • 4. Re: Sorting a column as a numeric value
                Madan N

                Thank you for the reply fac586.

                This is helping the column sort the value but as we are hiding the column, the users were unable to do sorting by themself.

                I have resolved this issue by using Interactive report and did not use apex_item.display_and_save() but used n001 column directly in the select statement. It is allowing me to sort as a numeric value.