VIEW: OLAPSYS.EXPORT2_JOIN_KEY_COLUMN_USES

Source

View Text - Preformatted

select
  d.owner owner,
  d.name dimension_name,
--  (case when h.hidden = 'Y'
--        then null else h.name end) hierarchy_name,
  h.name hierarchy_name,
  l.name child_level_name,
  u.username table_owner,
  o.name table_name,
  ck.name key_column_name,
  cf.name foreign_key_column_name,
  dhlm.position position,
  null join_key_type /* join key type */
from olapsys.CwM2$dimension d,
     olapsys.CwM2$hierarchy h,
     olapsys.CwM2$level l,
     olapsys.CwM2$HierLevelRel hlr,
     olapsys.CwM2$DimHierLvlMap dhlm,
     dba_users u,
     sys.obj$ o,
     sys.col$ ck,
     sys.col$ cf
where h.dimension_irid = d.irid and
      h.irid = hlr.hierarchy_irid and
      l.irid = hlr.childlevel_irid and
      dhlm.DimHierLvl_IRID = hlr.irid and
      dhlm.object_ID = o.obj# and
      dhlm.column_id = ck.col# and
      dhlm.object_ID = ck.obj# and
      dhlm.parentcolumn_id = cf.col#(+) and
      dhlm.object_ID = cf.obj#(+) and
      o.owner# = u.user_id
--    dhlm.style = 'STAR'
UNION
select d.owner owner,
  d.name dimension_name,
--  (case when h.hidden = 'Y'
--        then null else h.name end) hierarchy_name,
  h.name hierarchy_name,
  l.name child_level_name,
  u.username table_owner,
  o.name table_name,
  ck.name key_column_name,
  cf.name foreign_key_column_name,
  dhlm.position position,
  null join_key_type /* join key type */
from olapsys.CwM2$dimension d,
     olapsys.CwM2$hierarchy h,
     olapsys.CwM2$level l,
     olapsys.CwM2$HierLevelRel hlr,
     olapsys.CwM2$DimHierLvlMap dhlm,
     dba_users u,
     sys.obj$ o,
     sys.col$ ck,
     sys.col$ cf
where h.dimension_irid = d.irid and
      h.irid = hlr.hierarchy_irid and
      l.irid = hlr.childlevel_irid and
      dhlm.DimHierLvl_IRID = hlr.irid and
      dhlm.object_ID = o.obj# and
      dhlm.column_id = ck.col#(+) and
      dhlm.object_ID = ck.obj#(+) and
      dhlm.parentcolumn_id = cf.col# and
      dhlm.object_ID = cf.obj# and
      o.owner# = u.user_id
--    dhlm.style = 'SNOWFLAKE'
with read only

View Text - HTML Formatted

SELECT D.OWNER OWNER
, D.NAME DIMENSION_NAME
, -- (CASE WHEN H.HIDDEN = 'Y' -- THEN NULL ELSE H.NAME END) HIERARCHY_NAME
, H.NAME HIERARCHY_NAME
, L.NAME CHILD_LEVEL_NAME
, U.USERNAME TABLE_OWNER
, O.NAME TABLE_NAME
, CK.NAME KEY_COLUMN_NAME
, CF.NAME FOREIGN_KEY_COLUMN_NAME
, DHLM.POSITION POSITION
, NULL JOIN_KEY_TYPE /* JOIN KEY TYPE */ FROM OLAPSYS.CWM2$DIMENSION D
, OLAPSYS.CWM2$HIERARCHY H
, OLAPSYS.CWM2$LEVEL L
, OLAPSYS.CWM2$HIERLEVELREL HLR
, OLAPSYS.CWM2$DIMHIERLVLMAP DHLM
, DBA_USERS U
, SYS.OBJ$ O
, SYS.COL$ CK
, SYS.COL$ CF WHERE H.DIMENSION_IRID = D.IRID AND H.IRID = HLR.HIERARCHY_IRID AND L.IRID = HLR.CHILDLEVEL_IRID AND DHLM.DIMHIERLVL_IRID = HLR.IRID AND DHLM.OBJECT_ID = O.OBJ# AND DHLM.COLUMN_ID = CK.COL# AND DHLM.OBJECT_ID = CK.OBJ# AND DHLM.PARENTCOLUMN_ID = CF.COL#(+) AND DHLM.OBJECT_ID = CF.OBJ#(+) AND O.OWNER# = U.USER_ID -- DHLM.STYLE = 'STAR' UNION SELECT D.OWNER OWNER
, D.NAME DIMENSION_NAME
, -- (CASE WHEN H.HIDDEN = 'Y' -- THEN NULL ELSE H.NAME END) HIERARCHY_NAME
, H.NAME HIERARCHY_NAME
, L.NAME CHILD_LEVEL_NAME
, U.USERNAME TABLE_OWNER
, O.NAME TABLE_NAME
, CK.NAME KEY_COLUMN_NAME
, CF.NAME FOREIGN_KEY_COLUMN_NAME
, DHLM.POSITION POSITION
, NULL JOIN_KEY_TYPE /* JOIN KEY TYPE */ FROM OLAPSYS.CWM2$DIMENSION D
, OLAPSYS.CWM2$HIERARCHY H
, OLAPSYS.CWM2$LEVEL L
, OLAPSYS.CWM2$HIERLEVELREL HLR
, OLAPSYS.CWM2$DIMHIERLVLMAP DHLM
, DBA_USERS U
, SYS.OBJ$ O
, SYS.COL$ CK
, SYS.COL$ CF WHERE H.DIMENSION_IRID = D.IRID AND H.IRID = HLR.HIERARCHY_IRID AND L.IRID = HLR.CHILDLEVEL_IRID AND DHLM.DIMHIERLVL_IRID = HLR.IRID AND DHLM.OBJECT_ID = O.OBJ# AND DHLM.COLUMN_ID = CK.COL#(+) AND DHLM.OBJECT_ID = CK.OBJ#(+) AND DHLM.PARENTCOLUMN_ID = CF.COL# AND DHLM.OBJECT_ID = CF.OBJ# AND O.OWNER# = U.USER_ID -- DHLM.STYLE = 'SNOWFLAKE' WITH READ ONLY