This discussion is archived
6 Replies Latest reply: Feb 4, 2013 2:23 PM by Jen Hu RSS

Error with sort

Jen Hu Newbie
Currently Being Moderated
Hello,
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.
Jen
  • 1. Re: Error with sort
    Howard (... in Training) Pro
    Currently Being Moderated
    Jen,

    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.

    Howard
  • 2. Re: Error with sort
    Ramesh P, Oracle APEX Developer Journeyer
    Currently Being Moderated
    Hi,
    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 Explorer
    Currently Being Moderated
    Jen,

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

    /kenny
  • 4. Re: Error with sort
    Jen Hu Newbie
    Currently Being Moderated
    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.

    Jen
  • 5. Re: Error with sort
    Howard (... in Training) Pro
    Currently Being Moderated
    Jen,

    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.

    Questions:
    - 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.

    Howard
  • 6. Re: Error with sort
    Jen Hu Newbie
    Currently Being Moderated
    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.

    Thanks,
    Jen

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points