Oracle Analytics Cloud and Server

Home› Oracle Analytics› Oracle Analytics Forums› Oracle Analytics Cloud and Server› Oracle Analytics Cloud and Server

Categories

Welcome to the Oracle Analytics Community: Please complete your User Profile and upload your Profile Picture

Pivot Table Sorting Issue

Received Response
11
Views
7
Comments
User_2NI9B
User_2NI9B Rank 3 - Community Apprentice
edited Aug 13, 2024 5:55PM in Oracle Analytics Cloud and Server

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

====                                                                                                                                              ====

  pastedImage_6.png                                                 pastedImage_5.png

                                                                                                                                                         

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

Answers

  • [Deleted User]
    [Deleted User] Rank 2 - Community Beginner

    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
    User_2NI9B Rank 3 - Community Apprentice

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

  • [Deleted User]
    [Deleted User] Rank 2 - Community Beginner

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

  • User_2NI9B
    User_2NI9B Rank 3 - Community Apprentice

    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]
    [Deleted User] Rank 2 - Community Beginner

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

  • User_2NI9B
    User_2NI9B Rank 3 - Community Apprentice

    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]
    [Deleted User] Rank 2 - Community Beginner

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