My Oracle Support Banner

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

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.