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.

[Home] [Help]

VIEW: SYS.USER_CUBE_HIER_VIEW_COLUMNS

Source

View Text - Preformatted


SELECT
  do.name DIMENSION_NAME,
  h.hierarchy_name HIERARCHY_NAME,
  vo.name VIEW_NAME,
  col.name COLUMN_NAME,
  DECODE(avc.column_type, 2, 'KEY',
                          3, 'PARENT',
                          4, 'LEVEL_NAME',
                          5, 'DEPTH',
                          8, 'HIER_ORDER',
                          9, 'MEMBER_TYPE') COLUMN_TYPE,
  NULL OBJECT_NAME
FROM
  olap_aw_view_columns$ avc,
  olap_aw_views$ av,
  olap_hierarchies$ h,
  col$ col,
  obj$ do,
  obj$ vo
WHERE
  avc.view_obj# = av.view_obj#
  AND avc.column_type IN (2, 3, 4, 5, 8, 9)
  AND av.olap_object_type = 13 --HIERARCHY
  AND av.olap_object_id = h.hierarchy_id
  AND av.view_type = 1 -- ET
  AND h.dim_obj# = do.obj#
  AND do.owner# = USERENV('SCHEMAID')
  AND avc.view_obj# = col.obj#
  AND avc.column_obj# = col.col#
  AND av.view_obj# = vo.obj#
  AND vo.type# != 10 -- not NON-EXISTENT
  AND vo.owner# = do.owner#
UNION ALL
SELECT
  do.name DIMENSION_NAME,
  h.hierarchy_name HIERARCHY_NAME,
  vo.name VIEW_NAME,
  col.name COLUMN_NAME,
  'ATTRIBUTE' COLUMN_TYPE,
  a.attribute_name OBJECT_NAME
FROM
  olap_aw_view_columns$ avc,
  olap_aw_views$ av,
  olap_hierarchies$ h,
  olap_attributes$ a,
  col$ col,
  obj$ do,
  obj$ vo
WHERE
  avc.view_obj# = av.view_obj#
  AND av.olap_object_type = 13 --HIERARCHY
  AND avc.column_type = 1 -- OBJECT
  AND avc.referenced_object_type = 15 --ATTRIBUTE
  AND avc.referenced_object_id = a.attribute_id
  AND av.olap_object_id = h.hierarchy_id
  AND av.view_type = 1 -- ET
  AND h.dim_obj# = do.obj#
  AND do.owner# = USERENV('SCHEMAID')
  AND avc.view_obj# = col.obj#
  AND avc.column_obj# = col.col#
  AND av.view_obj# = vo.obj#
  AND vo.type# != 10 -- not NON-EXISTENT
  AND vo.owner# = do.owner#
UNION ALL
SELECT
  do.name DIMENSION_NAME,
  h.hierarchy_name HIERARCHY_NAME,
  vo.name VIEW_NAME,
  col.name COLUMN_NAME,
  'LEVEL' COLUMN_TYPE,
  dl.level_name OBJECT_NAME
FROM
  olap_aw_view_columns$ avc,
  olap_aw_views$ av,
  olap_hierarchies$ h,
  olap_hier_levels$ l,
  olap_dim_levels$ dl,
  col$ col,
  obj$ do,
  obj$ vo
WHERE
  avc.view_obj# = av.view_obj#
  AND av.olap_object_type = 13 --HIERARCHY
  AND avc.column_type = 1 -- OBJECT
  AND avc.referenced_object_type = 12 --DIM_LEVEL
  AND avc.referenced_object_id = dl.level_id
  AND l.dim_level_id = dl.level_id
  AND l.hierarchy_id = h.hierarchy_id
  AND av.olap_object_id = h.hierarchy_id
  AND av.view_type = 1 -- ET
  AND h.dim_obj# = do.obj#
  AND do.owner# = USERENV('SCHEMAID')
  AND avc.view_obj# = col.obj#
  AND avc.column_obj# = col.col#
  AND av.view_obj# = vo.obj#
  AND vo.type# != 10 -- not NON-EXISTENT
  AND vo.owner# = do.owner#

View Text - HTML Formatted

SELECT DO.NAME DIMENSION_NAME
, H.HIERARCHY_NAME HIERARCHY_NAME
, VO.NAME VIEW_NAME
, COL.NAME COLUMN_NAME
, DECODE(AVC.COLUMN_TYPE
, 2
, 'KEY'
, 3
, 'PARENT'
, 4
, 'LEVEL_NAME'
, 5
, 'DEPTH'
, 8
, 'HIER_ORDER'
, 9
, 'MEMBER_TYPE') COLUMN_TYPE
, NULL OBJECT_NAME FROM OLAP_AW_VIEW_COLUMNS$ AVC
, OLAP_AW_VIEWS$ AV
, OLAP_HIERARCHIES$ H
, COL$ COL
, OBJ$ DO
, OBJ$ VO WHERE AVC.VIEW_OBJ# = AV.VIEW_OBJ#
AND AVC.COLUMN_TYPE IN (2
, 3
, 4
, 5
, 8
, 9)
AND AV.OLAP_OBJECT_TYPE = 13 --HIERARCHY
AND AV.OLAP_OBJECT_ID = H.HIERARCHY_ID
AND AV.VIEW_TYPE = 1 -- ET
AND H.DIM_OBJ# = DO.OBJ#
AND DO.OWNER# = USERENV('SCHEMAID')
AND AVC.VIEW_OBJ# = COL.OBJ#
AND AVC.COLUMN_OBJ# = COL.COL#
AND AV.VIEW_OBJ# = VO.OBJ#
AND VO.TYPE# != 10 -- NOT NON-EXISTENT
AND VO.OWNER# = DO.OWNER# UNION ALL SELECT DO.NAME DIMENSION_NAME
, H.HIERARCHY_NAME HIERARCHY_NAME
, VO.NAME VIEW_NAME
, COL.NAME COLUMN_NAME
, 'ATTRIBUTE' COLUMN_TYPE
, A.ATTRIBUTE_NAME OBJECT_NAME FROM OLAP_AW_VIEW_COLUMNS$ AVC
, OLAP_AW_VIEWS$ AV
, OLAP_HIERARCHIES$ H
, OLAP_ATTRIBUTES$ A
, COL$ COL
, OBJ$ DO
, OBJ$ VO WHERE AVC.VIEW_OBJ# = AV.VIEW_OBJ#
AND AV.OLAP_OBJECT_TYPE = 13 --HIERARCHY
AND AVC.COLUMN_TYPE = 1 -- OBJECT
AND AVC.REFERENCED_OBJECT_TYPE = 15 --ATTRIBUTE
AND AVC.REFERENCED_OBJECT_ID = A.ATTRIBUTE_ID
AND AV.OLAP_OBJECT_ID = H.HIERARCHY_ID
AND AV.VIEW_TYPE = 1 -- ET
AND H.DIM_OBJ# = DO.OBJ#
AND DO.OWNER# = USERENV('SCHEMAID')
AND AVC.VIEW_OBJ# = COL.OBJ#
AND AVC.COLUMN_OBJ# = COL.COL#
AND AV.VIEW_OBJ# = VO.OBJ#
AND VO.TYPE# != 10 -- NOT NON-EXISTENT
AND VO.OWNER# = DO.OWNER# UNION ALL SELECT DO.NAME DIMENSION_NAME
, H.HIERARCHY_NAME HIERARCHY_NAME
, VO.NAME VIEW_NAME
, COL.NAME COLUMN_NAME
, 'LEVEL' COLUMN_TYPE
, DL.LEVEL_NAME OBJECT_NAME FROM OLAP_AW_VIEW_COLUMNS$ AVC
, OLAP_AW_VIEWS$ AV
, OLAP_HIERARCHIES$ H
, OLAP_HIER_LEVELS$ L
, OLAP_DIM_LEVELS$ DL
, COL$ COL
, OBJ$ DO
, OBJ$ VO WHERE AVC.VIEW_OBJ# = AV.VIEW_OBJ#
AND AV.OLAP_OBJECT_TYPE = 13 --HIERARCHY
AND AVC.COLUMN_TYPE = 1 -- OBJECT
AND AVC.REFERENCED_OBJECT_TYPE = 12 --DIM_LEVEL
AND AVC.REFERENCED_OBJECT_ID = DL.LEVEL_ID
AND L.DIM_LEVEL_ID = DL.LEVEL_ID
AND L.HIERARCHY_ID = H.HIERARCHY_ID
AND AV.OLAP_OBJECT_ID = H.HIERARCHY_ID
AND AV.VIEW_TYPE = 1 -- ET
AND H.DIM_OBJ# = DO.OBJ#
AND DO.OWNER# = USERENV('SCHEMAID')
AND AVC.VIEW_OBJ# = COL.OBJ#
AND AVC.COLUMN_OBJ# = COL.COL#
AND AV.VIEW_OBJ# = VO.OBJ#
AND VO.TYPE# != 10 -- NOT NON-EXISTENT
AND VO.OWNER# = DO.OWNER#