VIEW: OLAPSYS.DBA$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'
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' WITH READ ONLY