My Oracle Support Banner

An Analysis Having Column Formulas With COUNT DISTINCT Functions Returns No Results After Upgrading Oracle Analytics Server (Doc ID 2883337.1)

Last updated on NOVEMBER 03, 2023

Applies to:

Oracle Analytics Server - Version 2022 (6.4) and later
Oracle Analytics Cloud - Version 106.2.0 and later
Information in this document applies to any platform.


An Analysis has columns with COUNT DISTINCT and aggregation functions in the custom formulas / expressions.  For example:  SUM(<Column-A>)/(CASE WHEN COUNT(DISTINCT <Column-B>) =0 THEN 1 ELSE COUNT(DISTINCT <Column-B>) END)

In the RPD, the associated Physical Database has the GROUP_BY_GROUPING_SETS_SUPPORTED feature enabled (Value checkbox is checked ON).

On Oracle Analytics Server 5.9, the Analysis returns results.

On Oracle Analytics Server 6.4, the Analysis returns no results. From review of the Session Log for the Analysis, it is observed that the Physical SQL generated by OBIS includes "grouping_id" and "group by grouping sets" and it returns 0 rows from the Database when executed. 


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.