VIEW: OLAPSYS.DBA$OLAP2_DIM_LEVELS_KEYMAPS

Source

View Text - Preformatted

select
       d.owner owner,
       d.name dimension_name,
  (case when h.hidden = 'N'
        then h.name else null end) hierarchy_name,
       l.name level_name,
       NVL(L.DISPLAYNAME, L.NAME) display_name,
       NVL(L.SHORTDESCRIPTION, L.NAME) short_description,
       NVL(L.DESCRIPTION, L.NAME) description,
       hlr.leveldepth pos,
       u.username table_owner,
       o.name table_name,
       c.name column_name,
 decode(c.type#, 1, decode(c.charsetform, 2, 'NVARCHAR2', 'VARCHAR2'),
                 2, decode(c.scale, null,
                           decode(c.precision#, null, 'NUMBER', 'FLOAT'),
                           'NUMBER'),
                 8, 'LONG',
                 9, decode(c.charsetform, 2, 'NCHAR VARYING', 'VARCHAR'),
                 12, 'DATE', 23, 'RAW', 24, 'LONG RAW',
                 69, 'ROWID',
                 96, decode(c.charsetform, 2, 'NCHAR', 'CHAR'),
                 105, 'MLSLABEL',
                 106, 'MLSLABEL',
                 112, decode(c.charsetform, 2, 'NCLOB', 'CLOB'),
                 113, 'BLOB', 114, 'BFILE', 115, 'CFILE',
                 178, 'TIME(' ||c.spare1|| ')',
                 179, 'TIME(' ||c.spare1|| ')' || ' WITH TIME ZONE',
                 180, 'TIMESTAMP(' ||c.spare1|| ')',
                 181, 'TIMESTAMP(' ||c.spare1|| ')' || ' WITH TIME ZONE',
                 182, 'INTERVAL YEAR(' ||c.spare2||') TO MONTH',
                 183, 'INTERVAL DAY(' ||c.spare2||') TO SECOND(' ||
                       c.spare1 || ')',
                 208, 'UROWID',
                 'UNDEFINED') data_type
, decode(c.length, null, 0, c.length) data_length
, decode(c.precision#, null, 0, c.precision#) data_precision,
       dhlm.position column_position
from olapsys.cwm2$dimension d,
     olapsys.cwm2$hierarchy h,
     olapsys.cwm2$level l,
     olapsys.cwm2$hierlevelrel hlr,
     olapsys.cwm2$dimhierlvlmap dhlm,
     dba_users u,
     sys.obj$ o,
     sys.col$ c
where h.dimension_irid = d.irid and
      h.irid = hlr.hierarchy_irid and
      l.irid = hlr.childlevel_irid and
      dhlm.dimhierlvl_irid = hlr.irid and
      dhlm.object_id = o.obj# and
      dhlm.column_id = c.col# and
      o.obj# = c.obj# and
      o.owner# = u.user_id
with read only

View Text - HTML Formatted

SELECT D.OWNER OWNER
, D.NAME DIMENSION_NAME
, (CASE WHEN H.HIDDEN = 'N' THEN H.NAME ELSE NULL END) HIERARCHY_NAME
, L.NAME LEVEL_NAME
, NVL(L.DISPLAYNAME
, L.NAME) DISPLAY_NAME
, NVL(L.SHORTDESCRIPTION
, L.NAME) SHORT_DESCRIPTION
, NVL(L.DESCRIPTION
, L.NAME) DESCRIPTION
, HLR.LEVELDEPTH POS
, U.USERNAME TABLE_OWNER
, O.NAME TABLE_NAME
, C.NAME COLUMN_NAME
, DECODE(C.TYPE#
, 1
, DECODE(C.CHARSETFORM
, 2
, 'NVARCHAR2'
, 'VARCHAR2')
, 2
, DECODE(C.SCALE
, NULL
, DECODE(C.PRECISION#
, NULL
, 'NUMBER'
, 'FLOAT')
, 'NUMBER')
, 8
, 'LONG'
, 9
, DECODE(C.CHARSETFORM
, 2
, 'NCHAR VARYING'
, 'VARCHAR')
, 12
, 'DATE'
, 23
, 'RAW'
, 24
, 'LONG RAW'
, 69
, 'ROWID'
, 96
, DECODE(C.CHARSETFORM
, 2
, 'NCHAR'
, 'CHAR')
, 105
, 'MLSLABEL'
, 106
, 'MLSLABEL'
, 112
, DECODE(C.CHARSETFORM
, 2
, 'NCLOB'
, 'CLOB')
, 113
, 'BLOB'
, 114
, 'BFILE'
, 115
, 'CFILE'
, 178
, 'TIME(' ||C.SPARE1|| ')'
, 179
, 'TIME(' ||C.SPARE1|| ')' || ' WITH TIME ZONE'
, 180
, 'TIMESTAMP(' ||C.SPARE1|| ')'
, 181
, 'TIMESTAMP(' ||C.SPARE1|| ')' || ' WITH TIME ZONE'
, 182
, 'INTERVAL YEAR(' ||C.SPARE2||') TO MONTH'
, 183
, 'INTERVAL DAY(' ||C.SPARE2||') TO SECOND(' || C.SPARE1 || ')'
, 208
, 'UROWID'
, 'UNDEFINED') DATA_TYPE
, DECODE(C.LENGTH
, NULL
, 0
, C.LENGTH) DATA_LENGTH
, DECODE(C.PRECISION#
, NULL
, 0
, C.PRECISION#) DATA_PRECISION
, DHLM.POSITION COLUMN_POSITION FROM OLAPSYS.CWM2$DIMENSION D
, OLAPSYS.CWM2$HIERARCHY H
, OLAPSYS.CWM2$LEVEL L
, OLAPSYS.CWM2$HIERLEVELREL HLR
, OLAPSYS.CWM2$DIMHIERLVLMAP DHLM
, DBA_USERS U
, SYS.OBJ$ O
, SYS.COL$ C WHERE H.DIMENSION_IRID = D.IRID AND H.IRID = HLR.HIERARCHY_IRID AND L.IRID = HLR.CHILDLEVEL_IRID AND DHLM.DIMHIERLVL_IRID = HLR.IRID AND DHLM.OBJECT_ID = O.OBJ# AND DHLM.COLUMN_ID = C.COL# AND O.OBJ# = C.OBJ# AND O.OWNER# = U.USER_ID WITH READ ONLY