I have a report which behaves very strangely but only for one particular user. The report runs fine when no columns are set as sort-by in the report attributes. As soon as I try and set a column to sort (any column, it doesn't matter) this user gets the error message:
failed to parse SQL query:
ORA-01403: no data found
appear where the report should be on the page. The rest of the page displays OK. No other users are affected. If I remove the sort then the report is OK for this user (unsorted though!)
The report is a structured query with custom column headings, nothing unusual, quite basic. I have tried exporting and reimporting the page but it doesn't fix it. Same behaviour for this particular page in our test system as well.
I could rebuild the page but I'd rather know what is wrong in case it happens to some other region.
What version of HTML DB are you using? There was a problem with the sort preferences in earlier versions â which required purging the sort preferences of a user. Also, what are your settings for the sort sequence on the report attributes page?
We are now 1.6 but it happened when we were 1.5 also. I can put a sort on any single column or combination of columns and it causes the problem. If there are no sorts at all the report displays. Only one user (that I know about) has this problem - how can I find out whether any other users experience this but just don't report it?
If other users donât report having this problem, then thereâs no other way to find out if they do unless you log into their accounts and try. Iâm still not clear whatâs causing the issue. It obviously seems to be related to the sort preferences. But why it only happens to one person (that you know of), I donât know. Have you had a look at the sort sequence setting of your report? If you could reproduce this in a workspace on htmldb.oracle.com, Iâd be happy to take a look,
I don't know if this is a clue but when I went to delete the users preferences there were lots of them there but none for the page causing the problem. Perhaps this is what was causing the ora-1403? Anyway, clearing all preferences removed this problem.
Can I safely remove every users preferences from time to time? What are the implications of this? We don't use "custom" preferences BTW.
The reports engine stores the sort settings in the user preferences. This is done so that when you return to a report, it maintains the earlier sort settings. Like in your response to the HTML DB studio posting, you would keep your report sorted by "Created On" descending or ascending even when you come back another time. It's save to remove those preferences; all that's going to happen is that your users will see the report using the report's default settings the next time they go to that page. I will try to investigate this problem further, whatever additional information you can provide, will be helpful,