Sunday 10 September 2017

ORA-00933 when editing a Apex report

Have you encountered this error before in working with Apex? I do regularly wen editing reports.
At first I have created a nice report. After getting the query right I make some columns invisible, change the header texts and do some formatting.
Then I run the report and it looks nice. No errors on the SQL.

Then I apply a few changes and suddenly the report will not show up anymore. Instead I get this ugly error message:

failed to parse SQL query:
ORA-00933: SQL command not properly ended

What happend? It took me some time to figure it out. In my case there was an order by in the report query. This means that none of the report columns may be sortable.
At first this was the case. But one column was changed from Hidden to Plain Text. And in this change the Sortable attribute was set to Yes:


And Apex gets confused when a report query has an order by and an item is also sortable. So the query Apex creates has an incorrect syntax, hence the error message.

Setting the sortable attribute of the item to No immediately solves the problem. The report executes again without any error message.

Happy Apexing...


No comments: