eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

VIEW: OLAPSYS.DBA$OLAP2UHIER_CUSTOM_SORT

Source

View Text - Preformatted


select u.username owner,
       o.name dimension_name,
       h.hiername hierarchy_name,
       tu.username table_owner,
       tn.name table_name,
       c.name column_name,
       hcs.position 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
  dba_users u,
  sys.obj$ o,
  sys.dim$ d,
  sys.hier$ h,
  cwm2$hiercustomsort hcs,
  dba_users tu,
  sys.obj$ tn,
  sys.col$ c
where
  u.user_id = o.owner# and
  o.type# = 43 and
  o.obj# = d.obj# and
  d.obj# = h.dimobj# and
  hcs.dimension_irid = d.obj# and
  hcs.hier_irid = h.hierid# and
  hcs.metadataversion = 'ONE' and
  hcs.tablename_id = tn.obj# and
  tu.user_id = tn.owner# and
  c.obj# = tn.obj# and
  c.col# = hcs.columnname_id
union
select u.username owner,
       o.name dimension_name,
       null hierarchy_name,
       tu.username table_owner,
       tn.name table_name,
       c.name column_name,
       hcs.position 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
  dba_users u,
  sys.obj$ o,
  sys.dim$ d,
  cwm2$hiercustomsort hcs,
  dba_users tu,
  sys.obj$ tn,
  sys.col$ c
where
  u.user_id = o.owner# and
  o.type# = 43 and
  o.obj# = d.obj# and
  hcs.dimension_irid = d.obj# and
  hcs.metadataversion = 'ONE' and
  hcs.tablename_id = tn.obj# and
  tu.user_id = tn.owner# and
  c.obj# = tn.obj# and
  c.col# = hcs.columnname_id
union all
select owner,
       dimension_name,
       hierarchy_name,
       table_owner,
       table_name,
       column_name,
       position,
       sort_pos,
       sort_order,
       null_order,
       data_type,
       data_length,
       data_precision
from dba$olap2_hier_custom_sort
with read only

View Text - HTML Formatted

SELECT U.USERNAME OWNER
, O.NAME DIMENSION_NAME
, H.HIERNAME HIERARCHY_NAME
, TU.USERNAME TABLE_OWNER
, TN.NAME TABLE_NAME
, C.NAME COLUMN_NAME
, HCS.POSITION 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 DBA_USERS U
, SYS.OBJ$ O
, SYS.DIM$ D
, SYS.HIER$ H
, CWM2$HIERCUSTOMSORT HCS
, DBA_USERS TU
, SYS.OBJ$ TN
, SYS.COL$ C WHERE U.USER_ID = O.OWNER# AND O.TYPE# = 43 AND O.OBJ# = D.OBJ# AND D.OBJ# = H.DIMOBJ# AND HCS.DIMENSION_IRID = D.OBJ# AND HCS.HIER_IRID = H.HIERID# AND HCS.METADATAVERSION = 'ONE' AND HCS.TABLENAME_ID = TN.OBJ# AND TU.USER_ID = TN.OWNER# AND C.OBJ# = TN.OBJ# AND C.COL# = HCS.COLUMNNAME_ID UNION SELECT U.USERNAME OWNER
, O.NAME DIMENSION_NAME
, NULL HIERARCHY_NAME
, TU.USERNAME TABLE_OWNER
, TN.NAME TABLE_NAME
, C.NAME COLUMN_NAME
, HCS.POSITION 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 DBA_USERS U
, SYS.OBJ$ O
, SYS.DIM$ D
, CWM2$HIERCUSTOMSORT HCS
, DBA_USERS TU
, SYS.OBJ$ TN
, SYS.COL$ C WHERE U.USER_ID = O.OWNER# AND O.TYPE# = 43 AND O.OBJ# = D.OBJ# AND HCS.DIMENSION_IRID = D.OBJ# AND HCS.METADATAVERSION = 'ONE' AND HCS.TABLENAME_ID = TN.OBJ# AND TU.USER_ID = TN.OWNER# AND C.OBJ# = TN.OBJ# AND C.COL# = HCS.COLUMNNAME_ID UNION ALL SELECT OWNER
, DIMENSION_NAME
, HIERARCHY_NAME
, TABLE_OWNER
, TABLE_NAME
, COLUMN_NAME
, POSITION
, SORT_POS
, SORT_ORDER
, NULL_ORDER
, DATA_TYPE
, DATA_LENGTH
, DATA_PRECISION FROM DBA$OLAP2_HIER_CUSTOM_SORT WITH READ ONLY