SQL Snippets ™: Tutorials for Oracle Developers

In this tutorial we examine restrictions related to both the DIMENSION BY and MEASURES components of the MODEL clause.

If you try to include columns in your SELECT clause which are not DIMENSION or MEASURE columns you will get an error.

select
  key     ,
  GROUP_1 ,  -- exists in table T, but it is not a DIMENSION or MEASURE column
  num_val
from
  t
model
  dimension by ( key )
  measures     ( num_val )
  rules        ()
order by
  key
;
  GROUP_1 ,  -- exists in table T, but it is not a DIMENSION or MEASURE column
  *
ERROR at line 3:
ORA-32614: illegal MODEL SELECT expression


 

If you try to use the same column for both a dimension and a measure you will get an error.

select
  key ,
  num_val
from
  t
model
  DIMENSION BY ( KEY )
  MEASURES     ( KEY, num_val )
  rules        ()
order by
  key
;
  key ,
  *
ERROR at line 2:
ORA-00957: duplicate column name


 

The constants NULL and '' (empty string), by themselves, are not allowed in DIMENSION BY or MEASURES clauses.

select
  key   ,
  key_3 ,
  num_val
from
  t
model
  dimension by ( key, NULL as key_3 )
  measures     ( num_val )
  rules        ( )
order by
  key
;
  dimension by ( key, NULL as key_3 )
                      *
ERROR at line 8:
ORA-01723: zero-length columns are not allowed


 

select
  key ,
  new_text_measure
from
  t
model
  dimension by ( key )
  measures     ( '' as new_text_measure )
  rules        ( )
order by
  key
;
  measures     ( '' as new_text_measure )
                 *
ERROR at line 8:
ORA-01723: zero-length columns are not allowed


 

Instead, use CAST to specify a datatype and length for NULL or ''.

set null "(null)"

select
  key ,
  key_3 ,
  new_text_measure
from
  t
where
  key = 1
model
  dimension by( key, CAST( NULL AS VARCHAR2(10) ) as key_3 )
  measures    ( CAST( '' AS VARCHAR2(15) ) as new_text_measure )
  rules       ( )
order by
  key
;
 

   KEY KEY_3      NEW_TEXT_MEASURE
------ ---------- --------------------
     1 (null)     (null)
 

Gotchas

Pay attention to implicit data type conversions. In the following example NEW_TEXT_MEASURE is implicitly assigned a datatype with a length of 13 characters, the length of the constant used in the MEASURES clause. Attempting to assign a value that is longer than 13 characters raises a ORA-25137 error.

select
  key ,
  new_text_measure
from
  t
where
  key = 1
model
  dimension by( key )
  measures    ( '13 CHARACTERS' as new_text_measure )
  rules       ( new_text_measure[any] = 'OVER 13 CHARACTERS' )
;
  measures    ( '13 CHARACTERS' as new_text_measure )
                *
ERROR at line 10:
ORA-25137: Data value out of range


 

Despite the error message's phrasing, it is really the value "OVER 13 CHARACTERS" which is out of range, not "13 CHARACTERS".

To avoid the error, use CAST to force a datatype long enough to contain the largest value in the result set.

select
  key ,
  new_text_measure
from
  t
where
  key = 1
model
  dimension by( key )
  measures    ( CAST( '13 CHARACTERS' as varchar2(4000) ) as new_text_measure )
  rules       ( new_text_measure[any] = 'OVER 13 CHARACTERS' )
;
 

   KEY NEW_TEXT_MEASURE
------ --------------------
     1 OVER 13 CHARACTERS