Oracle Analytics Server, sorting direct sql results

Question

When you drop columns from subject area, there is sort option appear (picture 1)

but if you use Direct sql, there is no option for sorting (picture 2) . If you put "order by" clause to direct sql text, no change

is there any way to sort direct sql results

Gianni Ceresa · Answer

It isn't as binary as you say…

An "ORDER BY" in the query, is respected, at least in a table view.

I just did a quick test in OAS 2024 to validate this:

Without the ORDER BY, the months wouldn't be in chronological order but in A-Z order. And as you can see the "calendar_month_number" used to sort the query isn't even part of the returned columns.

This stop working if I switch to a pivot for example, mostly because there the product does apply the default A-Z sorting on the various elements.

A DDR does imply to lose most of the functionalities you paid for by buy OAS.

Sorting the list of columns is one of those, there isn't a SORT supported. Therefore it's a bit of a lottery: trying to sort the query at the best you can, maybe even returning columns (generate columns) that will let you influence a natural sort by adding them in the views and hiding them. You need to be creative to get to the expected result.

But that's the price you pay by using a DDR (because your OAS is just doing the same as Oracle SQL Developer, which is free).

Sumanth V -Oracle · Answer

@User_G9NLS - There are some functions you can’t perform in analyses whose columns originated from direct database requests:

* Select columns in the Subject Areas pane, because you aren’t working with columns from a semantic model.                         
* Create groups or selection steps for this analysis.                       
* Specify conditional formatting for the columns.                     
* Sort the values in the retrieved columns using the Sort Ascending and Sort Descending options in the column header of a pivot table, table, or trellis view.

This limitation has been mentioned in the product documentation itself.

Reference: Visualizing Data and Building Reports in Oracle Analytics Cloud

Kindly consider filing an Enhancement Request in our Idea Lab by following the steps outlined in the document below, along with a business justification. Up-voting the request will help the product management team to consider it for future releases:

How To Create An Enhancement Request In Idea Lab For Oracle Analytics (Doc ID 2662737.1)