Results are Sorted Wrong When INDEXCOL is Used in the Expression of a Column and its Sort Order Column in Oracle Analytics
(Doc ID 2775515.1)
Last updated on MAY 11, 2021
Applies to:Oracle Analytics Server - Version 5.5.0 to 5.9.0 [Release 5]
Information in this document applies to any platform.
On upgrade from OBIEE 11g to OAS 5.5 / 5.9, some analyses are returning results that are incorrectly sorted.
It was identified that a column with a Column Expression in the RPD that references the INDEXCOL function, and which has a Sort Order Column that does the same, will return incorrectly sorted results. See a simplified example below:
Column "Year" has expression: INDEXCOL(0, "Year_Base")
Column "Year" has Sort Order Column: "Year_Sort"
Column "Year_Sort" has expression: INDEXCOL(0, CASE "Year_Base" WHEN 2021 THEN 0001 ELSE "Year_Base" END, CASE "Year_Base" WHEN 2021 THEN 9999 ELSE "Year_Base" END)
In the data source, the Year_Base column has values like: 2018, 2019, 2020, 2021, 2022, 2023
When an Analysis containing the Year column is run, the results are in the following sort order: 2018, 2019, 2020, 2021, 2022, 2023
This is incorrect, as the results should be in the following sort order (same as in OBIEE 11g): 2021, 2018, 2019, 2020, 2022, 2023
To view full details, sign in with your My Oracle Support account.
Don't have a My Oracle Support account? Click to get started!
In this Document