VIEW: SYS.USER_CUBE_DIM_VIEW_COLUMNS

Source

View Text - Preformatted

SELECT
  do.name DIMENSION_NAME,
  vo.name VIEW_NAME,
  col.name COLUMN_NAME,
  DECODE(avc.column_type, 2, 'KEY',
                          4, 'LEVEL_NAME',
                          7, 'DIM_ORDER',
                          9, 'MEMBER_TYPE') COLUMN_TYPE,
  NULL OBJECT_NAME -- no object name for these column types
FROM
  olap_aw_view_columns$ avc,
  olap_aw_views$ av,
  col$ col,
  obj$ do,
  obj$ vo
WHERE
  avc.view_obj# = av.view_obj#
  AND av.olap_object_type = 11 --DIMENSION
  AND av.olap_object_id = do.obj#
  AND av.view_type = 1 -- ET
  AND avc.column_type IN (2, 4, 6, 7, 9)
  AND avc.view_obj# = col.obj#
  AND avc.column_obj# = col.col#
  AND do.owner# = USERENV('SCHEMAID')
  AND avc.view_obj#=vo.obj#
  AND vo.type# != 10 -- not NON-EXISTENT
  AND vo.owner#=do.owner#
UNION ALL
SELECT
  do.name DIMENSION_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_attributes$ a,
  col$ col,
  obj$ do,
  obj$ vo
WHERE
  avc.view_obj# = av.view_obj#
  AND av.olap_object_type = 11 --DIMENSION
  AND av.olap_object_id = do.obj#
  AND av.view_type = 1 -- ET
  AND avc.column_type = 1 -- OBJECT
  AND avc.referenced_object_type = 15 --ATTRIBUTE
  AND avc.referenced_object_id = a.attribute_id
  AND avc.view_obj# = col.obj#
  AND avc.column_obj# = col.col#
  AND a.dim_obj# = do.obj#
  AND do.owner# = USERENV('SCHEMAID')
  AND avc.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
, VO.NAME VIEW_NAME
, COL.NAME COLUMN_NAME
, DECODE(AVC.COLUMN_TYPE
, 2
, 'KEY'
, 4
, 'LEVEL_NAME'
, 7
, 'DIM_ORDER'
, 9
, 'MEMBER_TYPE') COLUMN_TYPE
, NULL OBJECT_NAME -- NO OBJECT NAME FOR THESE COLUMN TYPES FROM OLAP_AW_VIEW_COLUMNS$ AVC
, OLAP_AW_VIEWS$ AV
, COL$ COL
, OBJ$ DO
, OBJ$ VO WHERE AVC.VIEW_OBJ# = AV.VIEW_OBJ#
AND AV.OLAP_OBJECT_TYPE = 11 --DIMENSION
AND AV.OLAP_OBJECT_ID = DO.OBJ#
AND AV.VIEW_TYPE = 1 -- ET
AND AVC.COLUMN_TYPE IN (2
, 4
, 6
, 7
, 9)
AND AVC.VIEW_OBJ# = COL.OBJ#
AND AVC.COLUMN_OBJ# = COL.COL#
AND DO.OWNER# = USERENV('SCHEMAID')
AND AVC.VIEW_OBJ#=VO.OBJ#
AND VO.TYPE# != 10 -- NOT NON-EXISTENT
AND VO.OWNER#=DO.OWNER# UNION ALL SELECT DO.NAME DIMENSION_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_ATTRIBUTES$ A
, COL$ COL
, OBJ$ DO
, OBJ$ VO WHERE AVC.VIEW_OBJ# = AV.VIEW_OBJ#
AND AV.OLAP_OBJECT_TYPE = 11 --DIMENSION
AND AV.OLAP_OBJECT_ID = DO.OBJ#
AND AV.VIEW_TYPE = 1 -- ET
AND AVC.COLUMN_TYPE = 1 -- OBJECT
AND AVC.REFERENCED_OBJECT_TYPE = 15 --ATTRIBUTE
AND AVC.REFERENCED_OBJECT_ID = A.ATTRIBUTE_ID
AND AVC.VIEW_OBJ# = COL.OBJ#
AND AVC.COLUMN_OBJ# = COL.COL#
AND A.DIM_OBJ# = DO.OBJ#
AND DO.OWNER# = USERENV('SCHEMAID')
AND AVC.VIEW_OBJ#=VO.OBJ#
AND VO.TYPE# != 10 -- NOT NON-EXISTENT
AND VO.OWNER#=DO.OWNER#