SELECT DISTINCT
ce.classification_irid descriptor_id
, c.name descriptor_name
, sch.physicalname entity_owner
, cub.physicalname entity_name
, null child_entity_name
, NULL secondary_child_entity_name
, cvp.parametername parameter_name
, cvp.parametervalue parameter_value
FROM /* CUBE */
cwm$classification c
, cwm$classificationentry ce
, cwm2$classificationvaluepair cvp
, cwm$classificationtype cty
, cwm$cube cub
, cwm$model sch
, dba_users u
, cwm$cubedimensionuse cdu
, cwm$dimension cd
, dba_users du
, sys.obj$ do
WHERE cty.irid = c.classificationtype_irid
AND cty.name <> 'ORACLE_OLAP_CATALOG'
AND c.irid = ce.classification_irid
AND ce.name = 'CUBE'
AND ce.element_irid = cub.irid
AND ce.irid = cvp.classentry_irid (+)
AND cub.datamodel_irid = sch.irid
AND sch.physicalname = u.username
AND cdu.cube_irid = cub.irid
AND cdu.dimension_owner = du.username
AND cdu.dimension_name = do.name
AND du.user_id = do.owner#
AND do.obj# = cd.irid
AND do.type# = 43
AND cd.irid = cdu.abstractdimension_irid
UNION ALL SELECT DISTINCT
ce.classification_irid descriptor_id
, c.name descriptor_name
, sch.physicalname entity_owner
, cub.physicalname entity_name
, msr.physicalname child_entity_name
, NULL secondary_child_entity_name
, cvp.parametername parameter_name
, cvp.parametervalue parameter_value
FROM /* MEASURE */
cwm$classification c
, cwm$classificationentry ce
, cwm$classificationtype cty
, cwm2$classificationvaluepair cvp
, cwm$measure msr
, cwm$cube cub
, cwm$model sch
, dba_users u
, cwm$cubedimensionuse cdu
, cwm$dimension cd
, dba_users du
, sys.obj$ do
WHERE cty.irid = c.classificationtype_irid
AND cty.name <> 'ORACLE_OLAP_CATALOG'
AND c.irid = ce.classification_irid
AND ce.name = 'MEASURE'
AND ce.element_irid = msr.irid
AND ce.irid = cvp.classentry_irid (+)
AND msr.itemcontainer_irid = cub.irid
AND cub.datamodel_irid = sch.irid
AND sch.physicalname = u.username
AND cdu.cube_irid = cub.irid
AND cdu.dimension_owner = du.username
AND cdu.dimension_name = do.name
AND du.user_id = do.owner#
AND do.obj# = cd.irid
AND do.type# = 43
AND cd.irid = cdu.abstractdimension_irid
UNION ALL SELECT
ce.classification_irid descriptor_id
, c.name descriptor_name
, u.username entity_owner
, d.name entity_name
, dat.physicalname child_entity_name
, NULL secondary_child_entity_name
, cvp.parametername parameter_name
, cvp.parametervalue parameter_value
FROM /* DIMENSIONATTRIBUTE */
cwm$classification c
, cwm$classificationentry ce
, cwm2$classificationvaluepair cvp
, cwm$classificationtype cty
, cwm$dimensionattribute dat
, sys.obj$ d
, dba_users u
WHERE cty.irid = c.classificationtype_irid
AND cty.name <> 'ORACLE_OLAP_CATALOG'
AND c.irid = ce.classification_irid
AND ce.name = 'DIMENSION ATTRIBUTE'
AND ce.irid = cvp.classentry_irid (+)
AND ce.element_irid = dat.irid
AND dat.itemcontainer_irid = d.obj#
AND d.type# = 43 /* DIMENSION */
AND d.owner# = u.user_id
UNION ALL SELECT
ce.classification_irid descriptor_id
, c.name descriptor_name
, u.username entity_owner
, d.name entity_name
, ce.secondary_object_name child_entity_name
, ce.tertiary_object_name secondary_child_entity_name
, cvp.parametername parameter_name
, cvp.parametervalue parameter_value
FROM /* LEVELATTRIBUTE */
cwm$classification c
, cwm$classificationentry ce
, cwm2$classificationvaluepair cvp
, cwm$classificationtype cty
, cwm$levelattribute lat
, cwm$level lvl
, sys.obj$ d
, dba_users u
WHERE cty.irid = c.classificationtype_irid
AND cty.name <> 'ORACLE_OLAP_CATALOG'
AND c.irid = ce.classification_irid
AND ce.name = 'LEVEL ATTRIBUTE'
AND ce.irid = cvp.classentry_irid (+)
AND ce.element_irid = lat.irid
AND lat.itemcontainer_irid = lvl.irid
AND lvl.dimension_irid = d.obj#
AND d.type# = 43 /* DIMENSION */
AND d.owner# = u.user_id
UNION ALL SELECT
ce.classification_irid descriptor_id
, c.name descriptor_name
, u.username entity_owner
, d.name entity_name
, ce.secondary_object_name child_entity_name
, null secondary_child_entity_name
, cvp.parametername parameter_name
, cvp.parametervalue parameter_value
FROM /* DIMENSION, LEVEL, HIERARCHY */
cwm$classification c
, cwm$classificationentry ce
, cwm$classificationtype cty
, cwm2$classificationvaluepair cvp
, sys.obj$ d
, dba_users u
WHERE cty.irid = c.classificationtype_irid
AND cty.name <> 'ORACLE_OLAP_CATALOG'
AND c.irid = ce.classification_irid
AND ce.irid = cvp.classentry_irid (+)
AND ce.name IN ('DIMENSION', 'LEVEL', 'HIERARCHY')
AND ce.element_irid = d.obj#
AND d.type# = 43 /* DIMENSION */
AND d.owner# = u.user_id
UNION ALL SELECT
ce.classification_irid descriptor_id
, c.name descriptor_name
, f.name entity_owner
, p.name entity_name
, null child_entity_name
, NULL secondary_child_entity_name
, null parameter_name
, null parameter_value
FROM /* FUNCTION PARAMETER */
cwm$classification c
, cwm$classificationentry ce
, cwm$classificationtype cty
, cwm$function f
, cwm$parameter p
WHERE cty.irid = c.classificationtype_irid
AND cty.name <> 'ORACLE_OLAP_CATALOG'
AND c.irid = ce.classification_irid
AND ce.name = 'PARAMETER'
AND ce.element_irid = p.irid
AND p.operation_irid = f.irid
UNION ALL
select
ce.classification_irid descriptor_id,
c.name descriptor_name,
cub.owner entity_owner,
cub.name entity_name,
null child_entity_name,
null secondary_child_entity_name,
cvp.parametername parameter_name,
cvp.parametervalue parameter_value
from /* CUBE2 */
cwm$classification c,
cwm$classificationentry ce,
cwm2$classificationvaluepair cvp,
cwm$classificationtype cty,
cwm2$cube cub
where cty.irid = c.classificationtype_irid and
cty.name <> 'ORACLE_OLAP2_CATALOG' and
c.irid = ce.classification_irid and
ce.name = 'CUBE2' and
ce.element_irid = cub.irid and
ce.irid = cvp.classentry_irid (+)
UNION ALL select
ce.classification_irid descriptor_id,
c.name descriptor_name,
cub.owner entity_owner,
cub.name entity_name,
meas.name child_entity_name,
null secondary_child_entity_name,
cvp.parametername parameter_name,
cvp.parametervalue parameter_value
from /* MEASURE2 */
cwm$classification c,
cwm$classificationentry ce,
cwm2$classificationvaluepair cvp,
cwm$classificationtype cty,
cwm2$cube cub,
cwm2$measure meas
where cty.irid = c.classificationtype_irid and
cty.name <> 'ORACLE_OLAP2_CATALOG' and
c.irid = ce.classification_irid and
ce.name = 'MEASURE2' and
ce.irid = cvp.classentry_irid (+) and
ce.element_irid = meas.irid and
meas.cube_irid = cub.irid
UNION ALL select
ce.classification_irid descriptor_id,
c.name descriptor_name,
dim.owner entity_owner,
dim.name entity_name,
dat.name child_entity_name,
null secondary_child_entity_name,
cvp.parametername parameter_name,
cvp.parametervalue parameter_value
from /* DIMENSION ATTRIBUTE2 */
cwm$classification c,
cwm$classificationentry ce,
cwm2$classificationvaluepair cvp,
cwm$classificationtype cty,
cwm2$dimension dim,
cwm2$dimensionattribute dat
where cty.irid = c.classificationtype_irid and
cty.name <> 'ORACLE_OLAP2_CATALOG' and
c.irid = ce.classification_irid and
ce.name = 'DIMENSION ATTRIBUTE2' and
ce.element_irid = dat.irid and
ce.irid = cvp.classentry_irid (+) and
dat.dimension_irid = dim.irid
UNION ALL select
ce.classification_irid descriptor_id,
c.name descriptor_name,
dim.owner entity_owner,
dim.name entity_name,
lvl.name child_entity_name,
lat.name secondary_child_entity_name,
cvp.parametername parameter_name,
cvp.parametervalue parameter_value
from /* LEVEL ATTRIBUTE2 */
cwm$classification c,
cwm$classificationentry ce,
cwm2$classificationvaluepair cvp,
cwm$classificationtype cty,
cwm2$dimension dim,
cwm2$dimensionattribute dat,
cwm2$levelattribute lat,
cwm2$level lvl
where cty.irid = c.classificationtype_irid and
cty.name not in ('ORACLE_OLAP2_CATALOG',
'ORACLE_OLAP_CATALOG') and
c.irid = ce.classification_irid and
ce.name = 'LEVEL ATTRIBUTE2' and
ce.element_irid = lat.irid and
ce.irid = cvp.classentry_irid (+) and
lat.dimattr_irid = dat.irid and
dat.dimension_irid = dim.irid and
lvl.irid = lat.level_irid
UNION ALL select
ce.classification_irid descriptor_id,
c.name descriptor_name,
dim.owner entity_owner,
dim.name entity_name,
ce.secondary_object_name child_entity_name,
null secondary_child_entity_name,
cvp.parametername parameter_name,
cvp.parametervalue parameter_value
from /* DIMENSION2 */
cwm$classification c,
cwm$classificationentry ce,
cwm2$classificationvaluepair cvp,
cwm$classificationtype cty,
cwm2$dimension dim
where cty.irid = c.classificationtype_irid and
cty.name not in ('ORACLE_OLAP2_CATALOG',
'ORACLE_OLAP_CATALOG') and
c.irid = ce.classification_irid and
ce.irid = cvp.classentry_irid (+) and
ce.name = 'DIMENSION2' and
ce.element_irid = dim.irid
UNION ALL select
ce.classification_irid descriptor_id,
c.name descriptor_name,
dim.owner entity_owner,
dim.name entity_name,
ce.secondary_object_name child_entity_name,
null secondary_child_entity_name,
cvp.parametername parameter_name,
cvp.parametervalue parameter_value
from /* HIERARCHY2 */
cwm$classification c,
cwm$classificationentry ce,
cwm2$classificationvaluepair cvp,
cwm$classificationtype cty,
cwm2$dimension dim,
cwm2$hierarchy hier
where cty.irid = c.classificationtype_irid and
cty.name not in ('ORACLE_OLAP2_CATALOG',
'ORACLE_OLAP_CATALOG') and
c.irid = ce.classification_irid and
ce.irid = cvp.classentry_irid (+) and
ce.name = 'HIERARCHY2' and
dim.irid = hier.dimension_irid and
ce.element_irid = hier.irid
UNION ALL select
ce.classification_irid descriptor_id,
c.name descriptor_name,
dim.owner entity_owner,
dim.name entity_name,
ce.secondary_object_name child_entity_name,
null secondary_child_entity_name,
cvp.parametername parameter_name,
cvp.parametervalue parameter_value
from /* LEVEL2 */
cwm$classification c,
cwm$classificationentry ce,
cwm2$classificationvaluepair cvp,
cwm$classificationtype cty,
cwm2$dimension dim,
cwm2$level lvl
where cty.irid = c.classificationtype_irid and
cty.name not in ('ORACLE_OLAP2_CATALOG',
'ORACLE_OLAP_CATALOG') and
c.irid = ce.classification_irid and
ce.irid = cvp.classentry_irid (+) and
ce.name = 'LEVEL2' and
dim.irid = lvl.dimension_irid and
ce.element_irid = lvl.irid
with read only
SELECT DISTINCT
CE.CLASSIFICATION_IRID DESCRIPTOR_ID
, C.NAME DESCRIPTOR_NAME
, SCH.PHYSICALNAME ENTITY_OWNER
, CUB.PHYSICALNAME ENTITY_NAME
, NULL CHILD_ENTITY_NAME
, NULL SECONDARY_CHILD_ENTITY_NAME
, CVP.PARAMETERNAME PARAMETER_NAME
, CVP.PARAMETERVALUE PARAMETER_VALUE
FROM /* CUBE */
CWM$CLASSIFICATION C
, CWM$CLASSIFICATIONENTRY CE
, CWM2$CLASSIFICATIONVALUEPAIR CVP
, CWM$CLASSIFICATIONTYPE CTY
, CWM$CUBE CUB
, CWM$MODEL SCH
, DBA_USERS U
, CWM$CUBEDIMENSIONUSE CDU
, CWM$DIMENSION CD
, DBA_USERS DU
, SYS.OBJ$ DO
WHERE CTY.IRID = C.CLASSIFICATIONTYPE_IRID
AND CTY.NAME <> 'ORACLE_OLAP_CATALOG'
AND C.IRID = CE.CLASSIFICATION_IRID
AND CE.NAME = 'CUBE'
AND CE.ELEMENT_IRID = CUB.IRID
AND CE.IRID = CVP.CLASSENTRY_IRID (+)
AND CUB.DATAMODEL_IRID = SCH.IRID
AND SCH.PHYSICALNAME = U.USERNAME
AND CDU.CUBE_IRID = CUB.IRID
AND CDU.DIMENSION_OWNER = DU.USERNAME
AND CDU.DIMENSION_NAME = DO.NAME
AND DU.USER_ID = DO.OWNER#
AND DO.OBJ# = CD.IRID
AND DO.TYPE# = 43
AND CD.IRID = CDU.ABSTRACTDIMENSION_IRID
UNION ALL SELECT DISTINCT
CE.CLASSIFICATION_IRID DESCRIPTOR_ID
, C.NAME DESCRIPTOR_NAME
, SCH.PHYSICALNAME ENTITY_OWNER
, CUB.PHYSICALNAME ENTITY_NAME
, MSR.PHYSICALNAME CHILD_ENTITY_NAME
, NULL SECONDARY_CHILD_ENTITY_NAME
, CVP.PARAMETERNAME PARAMETER_NAME
, CVP.PARAMETERVALUE PARAMETER_VALUE
FROM /* MEASURE */
CWM$CLASSIFICATION C
, CWM$CLASSIFICATIONENTRY CE
, CWM$CLASSIFICATIONTYPE CTY
, CWM2$CLASSIFICATIONVALUEPAIR CVP
, CWM$MEASURE MSR
, CWM$CUBE CUB
, CWM$MODEL SCH
, DBA_USERS U
, CWM$CUBEDIMENSIONUSE CDU
, CWM$DIMENSION CD
, DBA_USERS DU
, SYS.OBJ$ DO
WHERE CTY.IRID = C.CLASSIFICATIONTYPE_IRID
AND CTY.NAME <> 'ORACLE_OLAP_CATALOG'
AND C.IRID = CE.CLASSIFICATION_IRID
AND CE.NAME = 'MEASURE'
AND CE.ELEMENT_IRID = MSR.IRID
AND CE.IRID = CVP.CLASSENTRY_IRID (+)
AND MSR.ITEMCONTAINER_IRID = CUB.IRID
AND CUB.DATAMODEL_IRID = SCH.IRID
AND SCH.PHYSICALNAME = U.USERNAME
AND CDU.CUBE_IRID = CUB.IRID
AND CDU.DIMENSION_OWNER = DU.USERNAME
AND CDU.DIMENSION_NAME = DO.NAME
AND DU.USER_ID = DO.OWNER#
AND DO.OBJ# = CD.IRID
AND DO.TYPE# = 43
AND CD.IRID = CDU.ABSTRACTDIMENSION_IRID
UNION ALL SELECT
CE.CLASSIFICATION_IRID DESCRIPTOR_ID
, C.NAME DESCRIPTOR_NAME
, U.USERNAME ENTITY_OWNER
, D.NAME ENTITY_NAME
, DAT.PHYSICALNAME CHILD_ENTITY_NAME
, NULL SECONDARY_CHILD_ENTITY_NAME
, CVP.PARAMETERNAME PARAMETER_NAME
, CVP.PARAMETERVALUE PARAMETER_VALUE
FROM /* DIMENSIONATTRIBUTE */
CWM$CLASSIFICATION C
, CWM$CLASSIFICATIONENTRY CE
, CWM2$CLASSIFICATIONVALUEPAIR CVP
, CWM$CLASSIFICATIONTYPE CTY
, CWM$DIMENSIONATTRIBUTE DAT
, SYS.OBJ$ D
, DBA_USERS U
WHERE CTY.IRID = C.CLASSIFICATIONTYPE_IRID
AND CTY.NAME <> 'ORACLE_OLAP_CATALOG'
AND C.IRID = CE.CLASSIFICATION_IRID
AND CE.NAME = 'DIMENSION ATTRIBUTE'
AND CE.IRID = CVP.CLASSENTRY_IRID (+)
AND CE.ELEMENT_IRID = DAT.IRID
AND DAT.ITEMCONTAINER_IRID = D.OBJ#
AND D.TYPE# = 43 /* DIMENSION */
AND D.OWNER# = U.USER_ID
UNION ALL SELECT
CE.CLASSIFICATION_IRID DESCRIPTOR_ID
, C.NAME DESCRIPTOR_NAME
, U.USERNAME ENTITY_OWNER
, D.NAME ENTITY_NAME
, CE.SECONDARY_OBJECT_NAME CHILD_ENTITY_NAME
, CE.TERTIARY_OBJECT_NAME SECONDARY_CHILD_ENTITY_NAME
, CVP.PARAMETERNAME PARAMETER_NAME
, CVP.PARAMETERVALUE PARAMETER_VALUE
FROM /* LEVELATTRIBUTE */
CWM$CLASSIFICATION C
, CWM$CLASSIFICATIONENTRY CE
, CWM2$CLASSIFICATIONVALUEPAIR CVP
, CWM$CLASSIFICATIONTYPE CTY
, CWM$LEVELATTRIBUTE LAT
, CWM$LEVEL LVL
, SYS.OBJ$ D
, DBA_USERS U
WHERE CTY.IRID = C.CLASSIFICATIONTYPE_IRID
AND CTY.NAME <> 'ORACLE_OLAP_CATALOG'
AND C.IRID = CE.CLASSIFICATION_IRID
AND CE.NAME = 'LEVEL ATTRIBUTE'
AND CE.IRID = CVP.CLASSENTRY_IRID (+)
AND CE.ELEMENT_IRID = LAT.IRID
AND LAT.ITEMCONTAINER_IRID = LVL.IRID
AND LVL.DIMENSION_IRID = D.OBJ#
AND D.TYPE# = 43 /* DIMENSION */
AND D.OWNER# = U.USER_ID
UNION ALL SELECT
CE.CLASSIFICATION_IRID DESCRIPTOR_ID
, C.NAME DESCRIPTOR_NAME
, U.USERNAME ENTITY_OWNER
, D.NAME ENTITY_NAME
, CE.SECONDARY_OBJECT_NAME CHILD_ENTITY_NAME
, NULL SECONDARY_CHILD_ENTITY_NAME
, CVP.PARAMETERNAME PARAMETER_NAME
, CVP.PARAMETERVALUE PARAMETER_VALUE
FROM /* DIMENSION
, LEVEL
, HIERARCHY */
CWM$CLASSIFICATION C
, CWM$CLASSIFICATIONENTRY CE
, CWM$CLASSIFICATIONTYPE CTY
, CWM2$CLASSIFICATIONVALUEPAIR CVP
, SYS.OBJ$ D
, DBA_USERS U
WHERE CTY.IRID = C.CLASSIFICATIONTYPE_IRID
AND CTY.NAME <> 'ORACLE_OLAP_CATALOG'
AND C.IRID = CE.CLASSIFICATION_IRID
AND CE.IRID = CVP.CLASSENTRY_IRID (+)
AND CE.NAME IN ('DIMENSION'
, 'LEVEL'
, 'HIERARCHY')
AND CE.ELEMENT_IRID = D.OBJ#
AND D.TYPE# = 43 /* DIMENSION */
AND D.OWNER# = U.USER_ID
UNION ALL SELECT
CE.CLASSIFICATION_IRID DESCRIPTOR_ID
, C.NAME DESCRIPTOR_NAME
, F.NAME ENTITY_OWNER
, P.NAME ENTITY_NAME
, NULL CHILD_ENTITY_NAME
, NULL SECONDARY_CHILD_ENTITY_NAME
, NULL PARAMETER_NAME
, NULL PARAMETER_VALUE
FROM /* FUNCTION PARAMETER */
CWM$CLASSIFICATION C
, CWM$CLASSIFICATIONENTRY CE
, CWM$CLASSIFICATIONTYPE CTY
, CWM$FUNCTION F
, CWM$PARAMETER P
WHERE CTY.IRID = C.CLASSIFICATIONTYPE_IRID
AND CTY.NAME <> 'ORACLE_OLAP_CATALOG'
AND C.IRID = CE.CLASSIFICATION_IRID
AND CE.NAME = 'PARAMETER'
AND CE.ELEMENT_IRID = P.IRID
AND P.OPERATION_IRID = F.IRID
UNION ALL
SELECT
CE.CLASSIFICATION_IRID DESCRIPTOR_ID
,
C.NAME DESCRIPTOR_NAME
,
CUB.OWNER ENTITY_OWNER
,
CUB.NAME ENTITY_NAME
,
NULL CHILD_ENTITY_NAME
,
NULL SECONDARY_CHILD_ENTITY_NAME
,
CVP.PARAMETERNAME PARAMETER_NAME
,
CVP.PARAMETERVALUE PARAMETER_VALUE
FROM /* CUBE2 */
CWM$CLASSIFICATION C
,
CWM$CLASSIFICATIONENTRY CE
,
CWM2$CLASSIFICATIONVALUEPAIR CVP
,
CWM$CLASSIFICATIONTYPE CTY
,
CWM2$CUBE CUB
WHERE CTY.IRID = C.CLASSIFICATIONTYPE_IRID AND
CTY.NAME <> 'ORACLE_OLAP2_CATALOG' AND
C.IRID = CE.CLASSIFICATION_IRID AND
CE.NAME = 'CUBE2' AND
CE.ELEMENT_IRID = CUB.IRID AND
CE.IRID = CVP.CLASSENTRY_IRID (+)
UNION ALL SELECT
CE.CLASSIFICATION_IRID DESCRIPTOR_ID
,
C.NAME DESCRIPTOR_NAME
,
CUB.OWNER ENTITY_OWNER
,
CUB.NAME ENTITY_NAME
,
MEAS.NAME CHILD_ENTITY_NAME
,
NULL SECONDARY_CHILD_ENTITY_NAME
,
CVP.PARAMETERNAME PARAMETER_NAME
,
CVP.PARAMETERVALUE PARAMETER_VALUE
FROM /* MEASURE2 */
CWM$CLASSIFICATION C
,
CWM$CLASSIFICATIONENTRY CE
,
CWM2$CLASSIFICATIONVALUEPAIR CVP
,
CWM$CLASSIFICATIONTYPE CTY
,
CWM2$CUBE CUB
,
CWM2$MEASURE MEAS
WHERE CTY.IRID = C.CLASSIFICATIONTYPE_IRID AND
CTY.NAME <> 'ORACLE_OLAP2_CATALOG' AND
C.IRID = CE.CLASSIFICATION_IRID AND
CE.NAME = 'MEASURE2' AND
CE.IRID = CVP.CLASSENTRY_IRID (+) AND
CE.ELEMENT_IRID = MEAS.IRID AND
MEAS.CUBE_IRID = CUB.IRID
UNION ALL SELECT
CE.CLASSIFICATION_IRID DESCRIPTOR_ID
,
C.NAME DESCRIPTOR_NAME
,
DIM.OWNER ENTITY_OWNER
,
DIM.NAME ENTITY_NAME
,
DAT.NAME CHILD_ENTITY_NAME
,
NULL SECONDARY_CHILD_ENTITY_NAME
,
CVP.PARAMETERNAME PARAMETER_NAME
,
CVP.PARAMETERVALUE PARAMETER_VALUE
FROM /* DIMENSION ATTRIBUTE2 */
CWM$CLASSIFICATION C
,
CWM$CLASSIFICATIONENTRY CE
,
CWM2$CLASSIFICATIONVALUEPAIR CVP
,
CWM$CLASSIFICATIONTYPE CTY
,
CWM2$DIMENSION DIM
,
CWM2$DIMENSIONATTRIBUTE DAT
WHERE CTY.IRID = C.CLASSIFICATIONTYPE_IRID AND
CTY.NAME <> 'ORACLE_OLAP2_CATALOG' AND
C.IRID = CE.CLASSIFICATION_IRID AND
CE.NAME = 'DIMENSION ATTRIBUTE2' AND
CE.ELEMENT_IRID = DAT.IRID AND
CE.IRID = CVP.CLASSENTRY_IRID (+) AND
DAT.DIMENSION_IRID = DIM.IRID
UNION ALL SELECT
CE.CLASSIFICATION_IRID DESCRIPTOR_ID
,
C.NAME DESCRIPTOR_NAME
,
DIM.OWNER ENTITY_OWNER
,
DIM.NAME ENTITY_NAME
,
LVL.NAME CHILD_ENTITY_NAME
,
LAT.NAME SECONDARY_CHILD_ENTITY_NAME
,
CVP.PARAMETERNAME PARAMETER_NAME
,
CVP.PARAMETERVALUE PARAMETER_VALUE
FROM /* LEVEL ATTRIBUTE2 */
CWM$CLASSIFICATION C
,
CWM$CLASSIFICATIONENTRY CE
,
CWM2$CLASSIFICATIONVALUEPAIR CVP
,
CWM$CLASSIFICATIONTYPE CTY
,
CWM2$DIMENSION DIM
,
CWM2$DIMENSIONATTRIBUTE DAT
,
CWM2$LEVELATTRIBUTE LAT
,
CWM2$LEVEL LVL
WHERE CTY.IRID = C.CLASSIFICATIONTYPE_IRID AND
CTY.NAME NOT IN ('ORACLE_OLAP2_CATALOG'
,
'ORACLE_OLAP_CATALOG') AND
C.IRID = CE.CLASSIFICATION_IRID AND
CE.NAME = 'LEVEL ATTRIBUTE2' AND
CE.ELEMENT_IRID = LAT.IRID AND
CE.IRID = CVP.CLASSENTRY_IRID (+) AND
LAT.DIMATTR_IRID = DAT.IRID AND
DAT.DIMENSION_IRID = DIM.IRID AND
LVL.IRID = LAT.LEVEL_IRID
UNION ALL SELECT
CE.CLASSIFICATION_IRID DESCRIPTOR_ID
,
C.NAME DESCRIPTOR_NAME
,
DIM.OWNER ENTITY_OWNER
,
DIM.NAME ENTITY_NAME
,
CE.SECONDARY_OBJECT_NAME CHILD_ENTITY_NAME
,
NULL SECONDARY_CHILD_ENTITY_NAME
,
CVP.PARAMETERNAME PARAMETER_NAME
,
CVP.PARAMETERVALUE PARAMETER_VALUE
FROM /* DIMENSION2 */
CWM$CLASSIFICATION C
,
CWM$CLASSIFICATIONENTRY CE
,
CWM2$CLASSIFICATIONVALUEPAIR CVP
,
CWM$CLASSIFICATIONTYPE CTY
,
CWM2$DIMENSION DIM
WHERE CTY.IRID = C.CLASSIFICATIONTYPE_IRID AND
CTY.NAME NOT IN ('ORACLE_OLAP2_CATALOG'
,
'ORACLE_OLAP_CATALOG') AND
C.IRID = CE.CLASSIFICATION_IRID AND
CE.IRID = CVP.CLASSENTRY_IRID (+) AND
CE.NAME = 'DIMENSION2' AND
CE.ELEMENT_IRID = DIM.IRID
UNION ALL SELECT
CE.CLASSIFICATION_IRID DESCRIPTOR_ID
,
C.NAME DESCRIPTOR_NAME
,
DIM.OWNER ENTITY_OWNER
,
DIM.NAME ENTITY_NAME
,
CE.SECONDARY_OBJECT_NAME CHILD_ENTITY_NAME
,
NULL SECONDARY_CHILD_ENTITY_NAME
,
CVP.PARAMETERNAME PARAMETER_NAME
,
CVP.PARAMETERVALUE PARAMETER_VALUE
FROM /* HIERARCHY2 */
CWM$CLASSIFICATION C
,
CWM$CLASSIFICATIONENTRY CE
,
CWM2$CLASSIFICATIONVALUEPAIR CVP
,
CWM$CLASSIFICATIONTYPE CTY
,
CWM2$DIMENSION DIM
,
CWM2$HIERARCHY HIER
WHERE CTY.IRID = C.CLASSIFICATIONTYPE_IRID AND
CTY.NAME NOT IN ('ORACLE_OLAP2_CATALOG'
,
'ORACLE_OLAP_CATALOG') AND
C.IRID = CE.CLASSIFICATION_IRID AND
CE.IRID = CVP.CLASSENTRY_IRID (+) AND
CE.NAME = 'HIERARCHY2' AND
DIM.IRID = HIER.DIMENSION_IRID AND
CE.ELEMENT_IRID = HIER.IRID
UNION ALL SELECT
CE.CLASSIFICATION_IRID DESCRIPTOR_ID
,
C.NAME DESCRIPTOR_NAME
,
DIM.OWNER ENTITY_OWNER
,
DIM.NAME ENTITY_NAME
,
CE.SECONDARY_OBJECT_NAME CHILD_ENTITY_NAME
,
NULL SECONDARY_CHILD_ENTITY_NAME
,
CVP.PARAMETERNAME PARAMETER_NAME
,
CVP.PARAMETERVALUE PARAMETER_VALUE
FROM /* LEVEL2 */
CWM$CLASSIFICATION C
,
CWM$CLASSIFICATIONENTRY CE
,
CWM2$CLASSIFICATIONVALUEPAIR CVP
,
CWM$CLASSIFICATIONTYPE CTY
,
CWM2$DIMENSION DIM
,
CWM2$LEVEL LVL
WHERE CTY.IRID = C.CLASSIFICATIONTYPE_IRID AND
CTY.NAME NOT IN ('ORACLE_OLAP2_CATALOG'
,
'ORACLE_OLAP_CATALOG') AND
C.IRID = CE.CLASSIFICATION_IRID AND
CE.IRID = CVP.CLASSENTRY_IRID (+) AND
CE.NAME = 'LEVEL2' AND
DIM.IRID = LVL.DIMENSION_IRID AND
CE.ELEMENT_IRID = LVL.IRID
WITH READ ONLY
|
|
|