VIEW: SYS.USER_CUBE_MEASURES

Source

View Text - Preformatted

SELECT
  o.name CUBE_NAME,
  m.measure_name MEASURE_NAME,
  m.measure_id MEASURE_ID,
  ss.syntax_clob OVERRIDE_SOLVE_SPEC,
  DECODE(m.measure_type, 1, 'BASE', 2, 'DERIVED') MEASURE_TYPE,
  DECODE(m.measure_type, 2, s.syntax_clob) EXPRESSION,
  d.description_value DESCRIPTION,
  DECODE(m.type#, 1, decode(m.charsetform, 2, 'NVARCHAR2', 'VARCHAR2'),
                  2, decode(m.scale, null,
                            decode(m.precision#, null, 'NUMBER', 'FLOAT'),
                            'NUMBER'),
                  8, 'LONG',
                  9, decode(m.charsetform, 2, 'NCHAR VARYING', 'VARCHAR'),
                  12, 'DATE',
                  23, 'RAW', 24, 'LONG RAW',
                  69, 'ROWID',
                  96, decode(m.charsetform, 2, 'NCHAR', 'CHAR'),
                  100, 'BINARY_FLOAT',
                  101, 'BINARY_DOUBLE',
                  105, 'MLSLABEL',
                  106, 'MLSLABEL',
                  112, decode(m.charsetform, 2, 'NCLOB', 'CLOB'),
                  113, 'BLOB', 114, 'BFILE', 115, 'CFILE',
                  178, 'TIME(' ||m.scale|| ')',
                  179, 'TIME(' ||m.scale|| ')' || ' WITH TIME ZONE',
                  180, 'TIMESTAMP(' ||m.scale|| ')',
                  181, 'TIMESTAMP(' ||m.scale|| ')' || ' WITH TIME ZONE',
                  231, 'TIMESTAMP(' ||m.scale|| ')' || ' WITH LOCAL TIME ZONE',
                  182, 'INTERVAL YEAR(' ||m.precision#||') TO MONTH',
                  183, 'INTERVAL DAY(' ||m.precision#||') TO SECOND(' ||
                        m.scale || ')',
                  208, 'UROWID',
                  'UNDEFINED') DATA_TYPE,
  m.length DATA_LENGTH,
  m.precision# DATA_PRECISION,
  m.scale DATA_SCALE,
  io.option_value LOOP_VAR_OVERRIDE,
  io2.option_value LOOP_DENSE_OVERRIDE,
  io3.option_value LOOP_TYPE
FROM
  olap_measures$ m,
  obj$ o,
  olap_syntax$ ss,
  olap_syntax$ s,
  olap_impl_options$ io,
  olap_impl_options$ io2,
  olap_impl_options$ io3,
  (select d.* from olap_descriptions$ d, nls_session_parameters n where
        n.parameter = 'NLS_LANGUAGE'
        and d.description_type = 'Description'
        and d.owning_object_type = 2 --MEASURE
        and (d.language = n.value
             or d.language like n.value || '\_%' escape '\')) d
WHERE
  m.cube_obj#=o.obj# AND o.owner#=USERENV('SCHEMAID')
  AND m.measure_id=s.owner_id(+)
  AND m.measure_id=ss.owner_id(+)
  AND io.owning_objectid(+)=m.measure_id
  AND io.object_type(+)=2
  AND io.option_type(+)=51 -- LOOP_VAR_OVERRIDE
  AND io2.owning_objectid(+)=m.measure_id
  AND io2.object_type(+)=2
  AND io2.option_type(+)=52 -- LOOP_DENSE_OVERRIDE
  AND io3.owning_objectid(+)=m.measure_id
  AND io3.object_type(+)=2
  AND io3.option_type(+)=53 -- LOOP_TYPE
  AND m.is_hidden=0 --NOT HIDDEN
  AND s.owner_type(+)=2 --MEASURE
  AND s.ref_role(+)=14 --DERIVED_MEAS_EXPRESSION
  AND ss.owner_type(+)=2 --MEASURE
  AND ss.ref_role(+)=16 --CONSISTENT_SOLVE_SPEC
  AND m.measure_id=d.owning_object_id(+)

View Text - HTML Formatted

SELECT O.NAME CUBE_NAME
, M.MEASURE_NAME MEASURE_NAME
, M.MEASURE_ID MEASURE_ID
, SS.SYNTAX_CLOB OVERRIDE_SOLVE_SPEC
, DECODE(M.MEASURE_TYPE
, 1
, 'BASE'
, 2
, 'DERIVED') MEASURE_TYPE
, DECODE(M.MEASURE_TYPE
, 2
, S.SYNTAX_CLOB) EXPRESSION
, D.DESCRIPTION_VALUE DESCRIPTION
, DECODE(M.TYPE#
, 1
, DECODE(M.CHARSETFORM
, 2
, 'NVARCHAR2'
, 'VARCHAR2')
, 2
, DECODE(M.SCALE
, NULL
, DECODE(M.PRECISION#
, NULL
, 'NUMBER'
, 'FLOAT')
, 'NUMBER')
, 8
, 'LONG'
, 9
, DECODE(M.CHARSETFORM
, 2
, 'NCHAR VARYING'
, 'VARCHAR')
, 12
, 'DATE'
, 23
, 'RAW'
, 24
, 'LONG RAW'
, 69
, 'ROWID'
, 96
, DECODE(M.CHARSETFORM
, 2
, 'NCHAR'
, 'CHAR')
, 100
, 'BINARY_FLOAT'
, 101
, 'BINARY_DOUBLE'
, 105
, 'MLSLABEL'
, 106
, 'MLSLABEL'
, 112
, DECODE(M.CHARSETFORM
, 2
, 'NCLOB'
, 'CLOB')
, 113
, 'BLOB'
, 114
, 'BFILE'
, 115
, 'CFILE'
, 178
, 'TIME(' ||M.SCALE|| ')'
, 179
, 'TIME(' ||M.SCALE|| ')' || ' WITH TIME ZONE'
, 180
, 'TIMESTAMP(' ||M.SCALE|| ')'
, 181
, 'TIMESTAMP(' ||M.SCALE|| ')' || ' WITH TIME ZONE'
, 231
, 'TIMESTAMP(' ||M.SCALE|| ')' || ' WITH LOCAL TIME ZONE'
, 182
, 'INTERVAL YEAR(' ||M.PRECISION#||') TO MONTH'
, 183
, 'INTERVAL DAY(' ||M.PRECISION#||') TO SECOND(' || M.SCALE || ')'
, 208
, 'UROWID'
, 'UNDEFINED') DATA_TYPE
, M.LENGTH DATA_LENGTH
, M.PRECISION# DATA_PRECISION
, M.SCALE DATA_SCALE
, IO.OPTION_VALUE LOOP_VAR_OVERRIDE
, IO2.OPTION_VALUE LOOP_DENSE_OVERRIDE
, IO3.OPTION_VALUE LOOP_TYPE FROM OLAP_MEASURES$ M
, OBJ$ O
, OLAP_SYNTAX$ SS
, OLAP_SYNTAX$ S
, OLAP_IMPL_OPTIONS$ IO
, OLAP_IMPL_OPTIONS$ IO2
, OLAP_IMPL_OPTIONS$ IO3
, (SELECT D.*
FROM OLAP_DESCRIPTIONS$ D
, NLS_SESSION_PARAMETERS N WHERE N.PARAMETER = 'NLS_LANGUAGE'
AND D.DESCRIPTION_TYPE = 'DESCRIPTION'
AND D.OWNING_OBJECT_TYPE = 2 --MEASURE
AND (D.LANGUAGE = N.VALUE OR D.LANGUAGE LIKE N.VALUE || '\_%' ESCAPE '\')) D WHERE M.CUBE_OBJ#=O.OBJ#
AND O.OWNER#=USERENV('SCHEMAID')
AND M.MEASURE_ID=S.OWNER_ID(+)
AND M.MEASURE_ID=SS.OWNER_ID(+)
AND IO.OWNING_OBJECTID(+)=M.MEASURE_ID
AND IO.OBJECT_TYPE(+)=2
AND IO.OPTION_TYPE(+)=51 -- LOOP_VAR_OVERRIDE
AND IO2.OWNING_OBJECTID(+)=M.MEASURE_ID
AND IO2.OBJECT_TYPE(+)=2
AND IO2.OPTION_TYPE(+)=52 -- LOOP_DENSE_OVERRIDE
AND IO3.OWNING_OBJECTID(+)=M.MEASURE_ID
AND IO3.OBJECT_TYPE(+)=2
AND IO3.OPTION_TYPE(+)=53 -- LOOP_TYPE
AND M.IS_HIDDEN=0 --NOT HIDDEN
AND S.OWNER_TYPE(+)=2 --MEASURE
AND S.REF_ROLE(+)=14 --DERIVED_MEAS_EXPRESSION
AND SS.OWNER_TYPE(+)=2 --MEASURE
AND SS.REF_ROLE(+)=16 --CONSISTENT_SOLVE_SPEC
AND M.MEASURE_ID=D.OWNING_OBJECT_ID(+)