Pivot Table Sorting Issue

User_2NI9B

Hi All.

We are in the process of migrating from 11g to 12c (12.2.1.2.0 (Build BIPS-20160923092103 64-bit)).

One of the issues we came across in 12c is pivot table sorting does not behave the same way as in 11g. It does not seem to be a new feature.

Please see the figures below-

11g                                                                                                                                                 12c

====                                                                                                                                              ====

                                                  

                                                                                                                                                         

So, in 11g you can see the values are sorted the way they should be, descending order on the second column (201610) Whereas, in 12c, same query, once column is (201610) sorted in descending order, values starts from null and then notice the largest number appears much later (2,946,298).

We verified the SQL generated, but it seems the sorting happens only on the front end. So, not much help with the SQL.

Anyone any idea?

Thanks

[Deleted User]

Is your NQSConfig.ini using NULL_VALUES_SORT_FIRST=ON; on 12? Or it was changed in the DB Deatures in the RPD?

User_2NI9B

Thanks for your response. I checked both, NULL_VALUES_SORT_FIRST=OFF and DB feature is unchecked. Still the same problem.

[Deleted User]

Ok are your LOCALE, SORT_ORDER_LOCALE and SORT_TYPE settings also the same as in the ini from 11?

User_2NI9B

This what I found in both-

LOCALE = "english-usa"; 

SORT_ORDER_LOCALE = "english-usa"; 

SORT_TYPE = "binary";

Surprisingly, NULL_VALUES_SORT_FIRST=ON on 11g!!!

[Deleted User]

Hmmm 12 seems to always sort nulls first in spite of either parameter. Maybe raise an SR.

User_2NI9B

What is more interesting is that sorting in ascending order seems confusing too as it shows 0 as the least number and not null.

An SR is definitely on it's way...

Thanks

[Deleted User]

You gotta admit: sorting is haaaaard to get right as a functionality :-P