VIEW: SYS.DBA_DIM_CHILD_OF

Source

View Text - Preformatted

select u.name, o.name, h.hiername, chl.pos#,
       cdl.levelname,
       decode(phl.joinkeyid#, 0, NULL, phl.joinkeyid#),
       pdl.levelname
from sys.obj$ o, sys.user$ u, sys.hier$ h,
     sys.hierlevel$ phl, sys.hierlevel$ chl,
     sys.dimlevel$ pdl,  sys.dimlevel$ cdl
where phl.dimobj# = o.obj#
  and o.owner# = u.user#
  and phl.dimobj# = h.dimobj#
  and phl.hierid# = h.hierid#
  and phl.dimobj# = pdl.dimobj#
  and phl.levelid# = pdl.levelid#
  and phl.dimobj# = chl.dimobj#
  and phl.hierid# = chl.hierid#
  and phl.pos# = chl.pos# + 1
  and chl.dimobj# = cdl.dimobj#
  and chl.levelid# = cdl.levelid#
  AND (phl.joinkeyid# = 0
       OR (phl.joinkeyid# NOT IN
             (SELECT DISTINCT d.joinkeyid#
                FROM sys.dimjoinkey$ d
                WHERE phl.dimobj# = d.dimobj# AND phl.joinkeyid# = d.joinkeyid#
                      AND d.chdlevid# != chl.levelid#
             )
          )
      )

View Text - HTML Formatted

SELECT U.NAME
, O.NAME
, H.HIERNAME
, CHL.POS#
, CDL.LEVELNAME
, DECODE(PHL.JOINKEYID#
, 0
, NULL
, PHL.JOINKEYID#)
, PDL.LEVELNAME FROM SYS.OBJ$ O
, SYS.USER$ U
, SYS.HIER$ H
, SYS.HIERLEVEL$ PHL
, SYS.HIERLEVEL$ CHL
, SYS.DIMLEVEL$ PDL
, SYS.DIMLEVEL$ CDL WHERE PHL.DIMOBJ# = O.OBJ#
AND O.OWNER# = U.USER#
AND PHL.DIMOBJ# = H.DIMOBJ#
AND PHL.HIERID# = H.HIERID#
AND PHL.DIMOBJ# = PDL.DIMOBJ#
AND PHL.LEVELID# = PDL.LEVELID#
AND PHL.DIMOBJ# = CHL.DIMOBJ#
AND PHL.HIERID# = CHL.HIERID#
AND PHL.POS# = CHL.POS# + 1
AND CHL.DIMOBJ# = CDL.DIMOBJ#
AND CHL.LEVELID# = CDL.LEVELID#
AND (PHL.JOINKEYID# = 0 OR (PHL.JOINKEYID# NOT IN (SELECT DISTINCT D.JOINKEYID#
FROM SYS.DIMJOINKEY$ D
WHERE PHL.DIMOBJ# = D.DIMOBJ#
AND PHL.JOINKEYID# = D.JOINKEYID#
AND D.CHDLEVID# != CHL.LEVELID# ) ) )