SQL Language (MOSC)

MOSC Banner

SQL MODEL: IGNORE NAV doesn't apply inside aggregates?

edited Apr 6, 2012 5:27AM in SQL Language (MOSC) 1 commentAnswered

While testing some queries using SQL MODEL I discovered that IGNORE NAV behavior does not apply inside of aggregates. If this is not a bug, I think it's worth documenting as a considerating when using aggregates. Consider this simple example illustrating the issue:

with tab as
 (select 0 dim, null col from dual union all select 1, 1 from dual)
select dim, col, result_count_any, result_count_any_nvl
from tab
model
 ignore nav
 dimension by (dim)
 measures (col, 0 result_count_any, 0 result_count_any_nvl)
rules
(
 result_count_any[dim] = count(col)[ANY],
 result_count_any_nvl[dim] = count(nvl(col,0))[ANY]

Howdy, Stranger!

Log In

To view full details, sign in to My Oracle Support Community.

Register

Don't have a My Oracle Support Community account? Click here to get started.

Category Leaderboard

Top contributors this month

New to My Oracle Support Community? Visit our Welcome Center

MOSC Help Center