Queries with a MODEL clause cannot specify aggregate or analytic functions in their SELECT or ORDER BY lists. For example, this attempt generates an error.
select
group_1 ,
MIN( GROUP_2 ) as c_1 ,
MAX( GROUP_2 ) as c_2 ,
SUM( NUM_VAL ) as c_3
from
t
group by
group_1
model
dimension by( group_1 )
measures ( group_2, num_val )
rules ( )
order by
group_1
;
measures ( group_2, num_val )
*
ERROR at line 12:
ORA-00979: not a GROUP BY expression
If needed, these functions can instead be specified in the PARTITION BY, DIMENSION BY, MEASURES, or RULES clauses. The following examples demonstrate their use in DIMENSION BY and MEASURES.
Aggregate Example
column d_1 format a10
column d_2 format a10
column m_1 format 999999
select
group_1, d_1, d_2, m_1
from
t
group by
group_1
model
dimension by
(
group_1 ,
MIN( GROUP_2 ) as d_1 ,
MAX( GROUP_2 ) as d_2
)
measures
(
SUM( NUM_VAL ) as m_1
)
rules ( )
order by
group_1
;
GROUP_1 D_1 D_2 M_1
---------- ---------- ---------- -------
A a1 a3 600
B a1 a3 400
C a1 a2 100
a1 a2 1000
Analytic Example
break on group_1 skip 1 duplicates
column d_1 format a10
column d_2 format a10
column m_1 format 999999
select
group_1, group_2, d_1, d_2, num_val, m_1
from
t
model
dimension by
(
group_1 ,
group_2 ,
MIN( GROUP_2 ) OVER ( PARTITION BY GROUP_1 ) as d_1 ,
MAX( GROUP_2 ) OVER ( PARTITION BY GROUP_1 ) as d_2
)
measures
( num_val ,
SUM( NUM_VAL ) OVER ( PARTITION BY GROUP_1 ) as m_1
)
rules ( )
order by
group_1 ,
group_2
;
GROUP_1 GROUP_2 D_1 D_2 NUM_VAL M_1
---------- ---------- ---------- ---------- ------- -------
A a1 a1 a3 100 600
A a2 a1 a3 200 600
A a3 a1 a3 300 600
B a1 a1 a3 400
B a2 a1 a3 300 400
B a3 a1 a3 100 400
C a1 a1 a2 100 100
C a2 a1 a2 100
a1 a1 a2 200 1000
a2 a1 a2 800 1000
RULES
You can also use aggregate and analytic functions in the RULES clause, but it is not as straightforward as using them in DIMENSION BY and MEASURES is. See the tutorial Aggregate and Analytic Expressions for more information on this topic.