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_VIEW_COLUMNS

Source

View Text - Preformatted


SELECT
  co.name CUBE_NAME,
  vo.name VIEW_NAME,
  col.name COLUMN_NAME,
  'MEASURE' COLUMN_TYPE,
  m.measure_name OBJECT_NAME -- name of measure
FROM
  olap_aw_view_columns$ avc,
  olap_aw_views$ av,
  olap_measures$ m,
  col$ col,
  obj$ co,
  obj$ vo
WHERE
  av.olap_object_type = 1 -- CUBE
  AND av.olap_object_id = co.obj#
  AND av.view_type = 1 -- ET
  AND co.owner# = USERENV('SCHEMAID')
  AND av.view_obj# = avc.view_obj#
  AND avc.column_type = 1 -- OBJECT
  AND avc.referenced_object_type = 2 -- MEASURE
  AND avc.referenced_object_id = m.measure_id
  AND avc.view_obj# = col.obj#
  AND avc.column_obj# = col.col#
  AND avc.view_obj#=vo.obj#
  AND vo.type# != 10 -- not NON-EXISTENT
  AND vo.owner#=co.owner#
UNION ALL
SELECT -- dimensioned by dimension
  co.name CUBE_NAME,
  vo.name VIEW_NAME,
  col.name COLUMN_NAME,
  'KEY' COLUMN_TYPE,
  do.name OBJECT_NAME -- name of dimension
FROM
  olap_aw_view_columns$ avc,
  olap_aw_views$ av,
  col$ col,
  obj$ co,
  obj$ vo,
  obj$ do,
  olap_dimensionality$ d
WHERE
  av.olap_object_type = 1 -- CUBE
  AND av.olap_object_id = co.obj#
  AND av.view_type = 1 -- ET
  AND co.owner# = USERENV('SCHEMAID')
  AND av.view_obj# = avc.view_obj#
  AND avc.column_type = 2 -- KEY
  AND avc.referenced_object_type = 16 -- DIMENSIONALITY
  AND avc.referenced_object_id = d.dimensionality_id
  AND d.dimension_type = 11 -- DIMENSION
  AND d.dimension_id = do.obj#
  AND avc.view_obj# = col.obj#
  AND avc.column_obj# = col.col#
  AND avc.view_obj#=vo.obj#
  AND vo.type# != 10 -- not NON-EXISTENT
  AND vo.owner#=co.owner#
UNION ALL
SELECT -- dimensioned by dimension level
  co.name CUBE_NAME,
  vo.name VIEW_NAME,
  col.name COLUMN_NAME,
  'KEY' COLUMN_TYPE,
  do.name OBJECT_NAME -- name of dimension
FROM
  olap_aw_view_columns$ avc,
  olap_aw_views$ av,
  col$ col,
  obj$ co,
  obj$ vo,
  obj$ do,
  olap_dimensionality$ d,
  olap_dim_levels$ dl
WHERE
  av.olap_object_type = 1 -- CUBE
  AND av.olap_object_id = co.obj#
  AND av.view_type = 1 -- ET
  AND co.owner# = USERENV('SCHEMAID')
  AND av.view_obj# = avc.view_obj#
  AND avc.column_type = 2 -- KEY
  AND avc.referenced_object_type = 16 -- DIMENSIONALITY
  AND avc.referenced_object_id = d.dimensionality_id
  AND d.dimension_type = 12 -- DIM_LEVEL
  AND d.dimension_id = dl.level_id
  AND dl.dim_obj# = do.obj#
  AND avc.view_obj# = col.obj#
  AND avc.column_obj# = col.col#
  AND avc.view_obj#=vo.obj#
  AND vo.type# != 10 -- not NON-EXISTENT
  AND vo.owner#=co.owner#
UNION ALL
SELECT -- dimensioned by hierarchy
  co.name CUBE_NAME,
  vo.name VIEW_NAME,
  col.name COLUMN_NAME,
  'KEY' COLUMN_TYPE,
  do.name OBJECT_NAME -- name of dimension
FROM
  olap_aw_view_columns$ avc,
  olap_aw_views$ av,
  col$ col,
  obj$ co,
  obj$ vo,
  obj$ do,
  olap_dimensionality$ d,
  olap_hierarchies$ h
WHERE
  av.olap_object_type = 1 -- CUBE
  AND av.olap_object_id = co.obj#
  AND av.view_type = 1 -- ET
  AND co.owner# = USERENV('SCHEMAID')
  AND av.view_obj# = avc.view_obj#
  AND avc.column_type = 2 -- KEY
  AND avc.referenced_object_type = 16 -- DIMENSIONALITY
  AND avc.referenced_object_id = d.dimensionality_id
  AND d.dimension_type = 13 -- HIERARCHY
  AND d.dimension_id = h.hierarchy_id
  AND h.dim_obj# = do.obj#
  AND avc.view_obj# = col.obj#
  AND avc.column_obj# = col.col#
  AND avc.view_obj#=vo.obj#
  AND vo.type# != 10 -- not NON-EXISTENT
  AND vo.owner#=co.owner#
UNION ALL
SELECT -- dimensioned by hierarchy level
  co.name CUBE_NAME,
  vo.name VIEW_NAME,
  col.name COLUMN_NAME,
  'KEY' COLUMN_TYPE,
  do.name OBJECT_NAME -- name of dimension
FROM
  olap_aw_view_columns$ avc,
  olap_aw_views$ av,
  col$ col,
  obj$ co,
  obj$ vo,
  obj$ do,
  olap_dimensionality$ d,
  olap_hierarchies$ h,
  olap_hier_levels$ hl
WHERE
  av.olap_object_type = 1 -- CUBE
  AND av.olap_object_id = co.obj#
  AND av.view_type = 1 -- ET
  AND co.owner# = USERENV('SCHEMAID')
  AND av.view_obj# = avc.view_obj#
  AND avc.column_type = 2 -- KEY
  AND avc.referenced_object_type = 16 -- DIMENSIONALITY
  AND avc.referenced_object_id = d.dimensionality_id
  AND d.dimension_type = 14 -- HIER_LEVEL
  AND d.dimension_id = hl.hierarchy_level_id
  AND hl.hierarchy_id = h.hierarchy_id
  AND h.dim_obj# = do.obj#
  AND avc.view_obj# = col.obj#
  AND avc.column_obj# = col.col#
  AND avc.view_obj#=vo.obj#
  AND vo.type# != 10 -- not NON-EXISTENT
  AND vo.owner#=co.owner#

View Text - HTML Formatted

SELECT CO.NAME CUBE_NAME
, VO.NAME VIEW_NAME
, COL.NAME COLUMN_NAME
, 'MEASURE' COLUMN_TYPE
, M.MEASURE_NAME OBJECT_NAME -- NAME OF MEASURE FROM OLAP_AW_VIEW_COLUMNS$ AVC
, OLAP_AW_VIEWS$ AV
, OLAP_MEASURES$ M
, COL$ COL
, OBJ$ CO
, OBJ$ VO WHERE AV.OLAP_OBJECT_TYPE = 1 -- CUBE
AND AV.OLAP_OBJECT_ID = CO.OBJ#
AND AV.VIEW_TYPE = 1 -- ET
AND CO.OWNER# = USERENV('SCHEMAID')
AND AV.VIEW_OBJ# = AVC.VIEW_OBJ#
AND AVC.COLUMN_TYPE = 1 -- OBJECT
AND AVC.REFERENCED_OBJECT_TYPE = 2 -- MEASURE
AND AVC.REFERENCED_OBJECT_ID = M.MEASURE_ID
AND AVC.VIEW_OBJ# = COL.OBJ#
AND AVC.COLUMN_OBJ# = COL.COL#
AND AVC.VIEW_OBJ#=VO.OBJ#
AND VO.TYPE# != 10 -- NOT NON-EXISTENT
AND VO.OWNER#=CO.OWNER# UNION ALL SELECT -- DIMENSIONED BY DIMENSION CO.NAME CUBE_NAME
, VO.NAME VIEW_NAME
, COL.NAME COLUMN_NAME
, 'KEY' COLUMN_TYPE
, DO.NAME OBJECT_NAME -- NAME OF DIMENSION FROM OLAP_AW_VIEW_COLUMNS$ AVC
, OLAP_AW_VIEWS$ AV
, COL$ COL
, OBJ$ CO
, OBJ$ VO
, OBJ$ DO
, OLAP_DIMENSIONALITY$ D WHERE AV.OLAP_OBJECT_TYPE = 1 -- CUBE
AND AV.OLAP_OBJECT_ID = CO.OBJ#
AND AV.VIEW_TYPE = 1 -- ET
AND CO.OWNER# = USERENV('SCHEMAID')
AND AV.VIEW_OBJ# = AVC.VIEW_OBJ#
AND AVC.COLUMN_TYPE = 2 -- KEY
AND AVC.REFERENCED_OBJECT_TYPE = 16 -- DIMENSIONALITY
AND AVC.REFERENCED_OBJECT_ID = D.DIMENSIONALITY_ID
AND D.DIMENSION_TYPE = 11 -- DIMENSION
AND D.DIMENSION_ID = DO.OBJ#
AND AVC.VIEW_OBJ# = COL.OBJ#
AND AVC.COLUMN_OBJ# = COL.COL#
AND AVC.VIEW_OBJ#=VO.OBJ#
AND VO.TYPE# != 10 -- NOT NON-EXISTENT
AND VO.OWNER#=CO.OWNER# UNION ALL SELECT -- DIMENSIONED BY DIMENSION LEVEL CO.NAME CUBE_NAME
, VO.NAME VIEW_NAME
, COL.NAME COLUMN_NAME
, 'KEY' COLUMN_TYPE
, DO.NAME OBJECT_NAME -- NAME OF DIMENSION FROM OLAP_AW_VIEW_COLUMNS$ AVC
, OLAP_AW_VIEWS$ AV
, COL$ COL
, OBJ$ CO
, OBJ$ VO
, OBJ$ DO
, OLAP_DIMENSIONALITY$ D
, OLAP_DIM_LEVELS$ DL WHERE AV.OLAP_OBJECT_TYPE = 1 -- CUBE
AND AV.OLAP_OBJECT_ID = CO.OBJ#
AND AV.VIEW_TYPE = 1 -- ET
AND CO.OWNER# = USERENV('SCHEMAID')
AND AV.VIEW_OBJ# = AVC.VIEW_OBJ#
AND AVC.COLUMN_TYPE = 2 -- KEY
AND AVC.REFERENCED_OBJECT_TYPE = 16 -- DIMENSIONALITY
AND AVC.REFERENCED_OBJECT_ID = D.DIMENSIONALITY_ID
AND D.DIMENSION_TYPE = 12 -- DIM_LEVEL
AND D.DIMENSION_ID = DL.LEVEL_ID
AND DL.DIM_OBJ# = DO.OBJ#
AND AVC.VIEW_OBJ# = COL.OBJ#
AND AVC.COLUMN_OBJ# = COL.COL#
AND AVC.VIEW_OBJ#=VO.OBJ#
AND VO.TYPE# != 10 -- NOT NON-EXISTENT
AND VO.OWNER#=CO.OWNER# UNION ALL SELECT -- DIMENSIONED BY HIERARCHY CO.NAME CUBE_NAME
, VO.NAME VIEW_NAME
, COL.NAME COLUMN_NAME
, 'KEY' COLUMN_TYPE
, DO.NAME OBJECT_NAME -- NAME OF DIMENSION FROM OLAP_AW_VIEW_COLUMNS$ AVC
, OLAP_AW_VIEWS$ AV
, COL$ COL
, OBJ$ CO
, OBJ$ VO
, OBJ$ DO
, OLAP_DIMENSIONALITY$ D
, OLAP_HIERARCHIES$ H WHERE AV.OLAP_OBJECT_TYPE = 1 -- CUBE
AND AV.OLAP_OBJECT_ID = CO.OBJ#
AND AV.VIEW_TYPE = 1 -- ET
AND CO.OWNER# = USERENV('SCHEMAID')
AND AV.VIEW_OBJ# = AVC.VIEW_OBJ#
AND AVC.COLUMN_TYPE = 2 -- KEY
AND AVC.REFERENCED_OBJECT_TYPE = 16 -- DIMENSIONALITY
AND AVC.REFERENCED_OBJECT_ID = D.DIMENSIONALITY_ID
AND D.DIMENSION_TYPE = 13 -- HIERARCHY
AND D.DIMENSION_ID = H.HIERARCHY_ID
AND H.DIM_OBJ# = DO.OBJ#
AND AVC.VIEW_OBJ# = COL.OBJ#
AND AVC.COLUMN_OBJ# = COL.COL#
AND AVC.VIEW_OBJ#=VO.OBJ#
AND VO.TYPE# != 10 -- NOT NON-EXISTENT
AND VO.OWNER#=CO.OWNER# UNION ALL SELECT -- DIMENSIONED BY HIERARCHY LEVEL CO.NAME CUBE_NAME
, VO.NAME VIEW_NAME
, COL.NAME COLUMN_NAME
, 'KEY' COLUMN_TYPE
, DO.NAME OBJECT_NAME -- NAME OF DIMENSION FROM OLAP_AW_VIEW_COLUMNS$ AVC
, OLAP_AW_VIEWS$ AV
, COL$ COL
, OBJ$ CO
, OBJ$ VO
, OBJ$ DO
, OLAP_DIMENSIONALITY$ D
, OLAP_HIERARCHIES$ H
, OLAP_HIER_LEVELS$ HL WHERE AV.OLAP_OBJECT_TYPE = 1 -- CUBE
AND AV.OLAP_OBJECT_ID = CO.OBJ#
AND AV.VIEW_TYPE = 1 -- ET
AND CO.OWNER# = USERENV('SCHEMAID')
AND AV.VIEW_OBJ# = AVC.VIEW_OBJ#
AND AVC.COLUMN_TYPE = 2 -- KEY
AND AVC.REFERENCED_OBJECT_TYPE = 16 -- DIMENSIONALITY
AND AVC.REFERENCED_OBJECT_ID = D.DIMENSIONALITY_ID
AND D.DIMENSION_TYPE = 14 -- HIER_LEVEL
AND D.DIMENSION_ID = HL.HIERARCHY_LEVEL_ID
AND HL.HIERARCHY_ID = H.HIERARCHY_ID
AND H.DIM_OBJ# = DO.OBJ#
AND AVC.VIEW_OBJ# = COL.OBJ#
AND AVC.COLUMN_OBJ# = COL.COL#
AND AVC.VIEW_OBJ#=VO.OBJ#
AND VO.TYPE# != 10 -- NOT NON-EXISTENT
AND VO.OWNER#=CO.OWNER#