VIEW: OLAPSYS.ALL$OLAP2_HIER_CUSTOM_SORT

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,
       u.username table_owner,
       o.name table_name,
       c.name column_name,
       hcs.position,
       hcs.SortPos sort_pos,
       hcs.SortOrder sort_order,
       hcs.NullOrder null_order,
 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
from olapsys.cwm2$hierarchy h,
     olapsys.cwm2$hiercustomsort hcs,
     olapsys.cwm2$dimension d,
     sys.obj$ o,
     dba_users u,
     sys.col$ c
where h.irid = hcs.hier_irid and
      h.dimension_irid = d.irid and
      hcs.tablename_id = o.obj# and
      u.user_id = o.owner# and
      c.obj# = o.obj# and
      c.col# = hcs.columnname_id and
      hcs.metadataversion = 'TWO' 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)))
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
, U.USERNAME TABLE_OWNER
, O.NAME TABLE_NAME
, C.NAME COLUMN_NAME
, HCS.POSITION
, HCS.SORTPOS SORT_POS
, HCS.SORTORDER SORT_ORDER
, HCS.NULLORDER NULL_ORDER
, 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 FROM OLAPSYS.CWM2$HIERARCHY H
, OLAPSYS.CWM2$HIERCUSTOMSORT HCS
, OLAPSYS.CWM2$DIMENSION D
, SYS.OBJ$ O
, DBA_USERS U
, SYS.COL$ C WHERE H.IRID = HCS.HIER_IRID AND H.DIMENSION_IRID = D.IRID AND HCS.TABLENAME_ID = O.OBJ# AND U.USER_ID = O.OWNER# AND C.OBJ# = O.OBJ# AND C.COL# = HCS.COLUMNNAME_ID AND HCS.METADATAVERSION = 'TWO' 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))) WITH READ ONLY