VIEW: OLAPSYS.ALL$OLAP2_DIM_LEVEL_ATTR_MAPS

Source

View Text - Preformatted

select attr.owner owner,
  attr.dimension_name dimension_name,
  (case when attr.hidden = 'N'
        then attr.hierarchy_name else null end) hierarchy_name,
  attr.attribute_name attribute_name,
  attr.lvl_attribute_name lvl_attribute_name,
  attr.level_name level_name,
  attr.table_owner table_owner,
  attr.table_name table_name,
  attr.column_name column_name,
  attr.dtype dtype,
  attr.data_length data_length,
  attr.data_precision data_precision,
 (case when attr.dtype = 'NUMBER' then 0
              when attr.dtype = 'DOUBLE' then 5
              when attr.dtype = 'FLOAT' then 4
              when attr.dtype = 'DATE' then 7
              when attr.dtype = 'LONG' then 3
              else 1 end) olap_api_data_type
from (
select
  d.owner owner,
  d.name dimension_name,
  h.name hierarchy_name,
  da.name attribute_name,
  la.name lvl_attribute_name,
  l.name level_name,
  u.username table_owner,
  o.name table_name,
  col.name column_name,
  h.hidden hidden,
decode(col.type#, 1, decode(col.charsetform, 2, 'NVARCHAR2', 'VARCHAR2'),
                 2, decode(col.scale, null,
                           decode(col.precision#, null, 'NUMBER', 'FLOAT'),
                           'NUMBER'),
                 8, 'LONG',
                 9, decode(col.charsetform, 2, 'NCHAR VARYING', 'VARCHAR'),
                 12, 'DATE', 23, 'RAW', 24, 'LONG RAW',
                 69, 'ROWID',
                 96, decode(col.charsetform, 2, 'NCHAR', 'CHAR'),
                 105, 'MLSLABEL',
                 106, 'MLSLABEL',
                 112, decode(col.charsetform, 2, 'NCLOB', 'CLOB'),
                 113, 'BLOB', 114, 'BFILE', 115, 'CFILE',
                 178, 'TIME(' ||col.spare1|| ')',
                 179, 'TIME(' ||col.spare1|| ')' || ' WITH TIME ZONE',
                 180, 'TIMESTAMP(' ||col.spare1|| ')',
                 181, 'TIMESTAMP(' ||col.spare1|| ')' || ' WITH TIME ZONE',
                 182, 'INTERVAL YEAR(' ||col.spare2||') TO MONTH',
                 183, 'INTERVAL DAY(' ||col.spare2||') TO SECOND(' ||
                       col.spare1 || ')',
                 208, 'UROWID',
                 'UNDEFINED') dtype
, decode(col.length, null, 0, col.length) data_length
, decode(col.precision#, null, 0, col.precision#) data_precision
from olapsys.CwM2$Dimension d,
     olapsys.cwm2$dimensionattribute da,
     olapsys.cwm2$level l,
     olapsys.CwM2$LevelAttribute la,
     olapsys.CwM2$LevelAttributeMap lam,
     olapsys.CwM2$Hierarchy h,
     olapsys.CwM2$HierLevelRel hlr,
     dba_users u,
     sys.obj$ o,
     sys.col$ col
where d.irid = da.dimension_irid and
      la.dimattr_irid = da.irid and
      la.level_irid = l.irid and
      la.irid = lam.levelattr_irid and
      lam.hierlvlrel_irid = hlr.irid and
      hlr.hierarchy_irid = h.irid and
      lam.Table_ID = o.obj# and
      lam.Column_ID = col.col# and
      o.obj# = col.obj# and
      o.owner# = u.user_id and
       d.invalid = 'N' and
       (cwm2$security.dimension_tables_visible(d.irid) = 'Y'
       OR EXISTS (select null from v$enabledprivs
                  where priv_number in (-47, -215, -216, -217)))) attr
with read only

View Text - HTML Formatted

SELECT ATTR.OWNER OWNER
, ATTR.DIMENSION_NAME DIMENSION_NAME
, (CASE WHEN ATTR.HIDDEN = 'N' THEN ATTR.HIERARCHY_NAME ELSE NULL END) HIERARCHY_NAME
, ATTR.ATTRIBUTE_NAME ATTRIBUTE_NAME
, ATTR.LVL_ATTRIBUTE_NAME LVL_ATTRIBUTE_NAME
, ATTR.LEVEL_NAME LEVEL_NAME
, ATTR.TABLE_OWNER TABLE_OWNER
, ATTR.TABLE_NAME TABLE_NAME
, ATTR.COLUMN_NAME COLUMN_NAME
, ATTR.DTYPE DTYPE
, ATTR.DATA_LENGTH DATA_LENGTH
, ATTR.DATA_PRECISION DATA_PRECISION
, (CASE WHEN ATTR.DTYPE = 'NUMBER' THEN 0 WHEN ATTR.DTYPE = 'DOUBLE' THEN 5 WHEN ATTR.DTYPE = 'FLOAT' THEN 4 WHEN ATTR.DTYPE = 'DATE' THEN 7 WHEN ATTR.DTYPE = 'LONG' THEN 3 ELSE 1 END) OLAP_API_DATA_TYPE FROM ( SELECT D.OWNER OWNER
, D.NAME DIMENSION_NAME
, H.NAME HIERARCHY_NAME
, DA.NAME ATTRIBUTE_NAME
, LA.NAME LVL_ATTRIBUTE_NAME
, L.NAME LEVEL_NAME
, U.USERNAME TABLE_OWNER
, O.NAME TABLE_NAME
, COL.NAME COLUMN_NAME
, H.HIDDEN HIDDEN
, DECODE(COL.TYPE#
, 1
, DECODE(COL.CHARSETFORM
, 2
, 'NVARCHAR2'
, 'VARCHAR2')
, 2
, DECODE(COL.SCALE
, NULL
, DECODE(COL.PRECISION#
, NULL
, 'NUMBER'
, 'FLOAT')
, 'NUMBER')
, 8
, 'LONG'
, 9
, DECODE(COL.CHARSETFORM
, 2
, 'NCHAR VARYING'
, 'VARCHAR')
, 12
, 'DATE'
, 23
, 'RAW'
, 24
, 'LONG RAW'
, 69
, 'ROWID'
, 96
, DECODE(COL.CHARSETFORM
, 2
, 'NCHAR'
, 'CHAR')
, 105
, 'MLSLABEL'
, 106
, 'MLSLABEL'
, 112
, DECODE(COL.CHARSETFORM
, 2
, 'NCLOB'
, 'CLOB')
, 113
, 'BLOB'
, 114
, 'BFILE'
, 115
, 'CFILE'
, 178
, 'TIME(' ||COL.SPARE1|| ')'
, 179
, 'TIME(' ||COL.SPARE1|| ')' || ' WITH TIME ZONE'
, 180
, 'TIMESTAMP(' ||COL.SPARE1|| ')'
, 181
, 'TIMESTAMP(' ||COL.SPARE1|| ')' || ' WITH TIME ZONE'
, 182
, 'INTERVAL YEAR(' ||COL.SPARE2||') TO MONTH'
, 183
, 'INTERVAL DAY(' ||COL.SPARE2||') TO SECOND(' || COL.SPARE1 || ')'
, 208
, 'UROWID'
, 'UNDEFINED') DTYPE
, DECODE(COL.LENGTH
, NULL
, 0
, COL.LENGTH) DATA_LENGTH
, DECODE(COL.PRECISION#
, NULL
, 0
, COL.PRECISION#) DATA_PRECISION FROM OLAPSYS.CWM2$DIMENSION D
, OLAPSYS.CWM2$DIMENSIONATTRIBUTE DA
, OLAPSYS.CWM2$LEVEL L
, OLAPSYS.CWM2$LEVELATTRIBUTE LA
, OLAPSYS.CWM2$LEVELATTRIBUTEMAP LAM
, OLAPSYS.CWM2$HIERARCHY H
, OLAPSYS.CWM2$HIERLEVELREL HLR
, DBA_USERS U
, SYS.OBJ$ O
, SYS.COL$ COL WHERE D.IRID = DA.DIMENSION_IRID AND LA.DIMATTR_IRID = DA.IRID AND LA.LEVEL_IRID = L.IRID AND LA.IRID = LAM.LEVELATTR_IRID AND LAM.HIERLVLREL_IRID = HLR.IRID AND HLR.HIERARCHY_IRID = H.IRID AND LAM.TABLE_ID = O.OBJ# AND LAM.COLUMN_ID = COL.COL# AND O.OBJ# = COL.OBJ# AND O.OWNER# = U.USER_ID AND D.INVALID = 'N' AND (CWM2$SECURITY.DIMENSION_TABLES_VISIBLE(D.IRID) = 'Y' OR EXISTS (SELECT NULL
FROM V$ENABLEDPRIVS
WHERE PRIV_NUMBER IN (-47
, -215
, -216
, -217)))) ATTR WITH READ ONLY