6 Replies Latest reply on Feb 4, 2013 10:23 PM by Jen Hu

    Error with sort

    Jen Hu
      I have a report with one column which is Select List(named LOV). I want to sort that column,
      however, i got an error: failed to parse SQL query:
      ORA-01403: no data found

      If i don't sort it, the report is working fine.

      What caused the problems? Thanks for your help.
        • 1. Re: Error with sort
          Howard (... in Training)

          Are you defining the LOV by a SELECT? Here is one of mine:
          select SYS_NM||'/'||NIC_NM as display_value, 
                 SYS_NM||'/'||NIC_NM as return_value 
            from NTWRK_PKT_CNT
           group by SYS_NM||'/'||NIC_NM, SYS_NM||'/'||NIC_NM  
           order by 1
          You can add an "order by" clause. Ignore the "group by" I was just getting fancy.

          • 2. Re: Error with sort
            Ramesh P, Oracle APEX Developer
            If SELECT col1, col2, col3 FROM table_x is your query where col2 is your select list based on Named LOV, then

            If you want to sort the report based on value selected in select list use this query

            SELECT col1, col2, col3 FROM table_x ORDER BY 2

            If you want to sort the values in the select list which should be something like this

            SELECT col1 as display_value, col2 as return_value from table_y order by 1
            • 3. Re: Error with sort
              Kenny Hanberg

              Are You using an APEX_ITEM.SELECT_LIST_FROM_LOV(), or similar function, in the report select statement to create the lov?

              • 4. Re: Error with sort
                Jen Hu
                Thank u all for your reply.
                If fact, i want to make it work this way: when the user click the title of that column, he can sort any way he want.

                • 5. Re: Error with sort
                  Howard (... in Training)

                  Normally -- as far as I know -- if you want the user to sort something, it's usually data in a report. APEX provides ways to do that. You can click on the report column header. I don't know of any standard feature to allow the user to sort an LOV -- but it would be an interesting feature.

                  - what sort options would you want: ascending, descending, other. .. ???
                  - where would they click to indicate how they wanted ascending, descending or other?

                  Unless someone provides these in some plug-in -- which I know nothing about -- you'd have to write you own HTML / CSS / javascript(?) code to implement these. At least, that's what I surmise.

                  But maybe someone knows better than me.

                  • 6. Re: Error with sort
                    Jen Hu
                    Sorry, maybe i don't make it clear. The column i want to sort is Editable in the report. After i check the sort attributes, i got the error message.