[Home] [Help]
View: EDW_FACT_ATTRIBUTES_MD_V
View Text
SELECT FACT.ELEMENTID FACT_ID
, FACT.NAME FACT_NAME
, DECODE((SELECT NAME
FROM CMPDATATYPE_V DATA
WHERE MEASURE.DOMAIN = DATA.ELEMENTID)
, 'NUMBER'
, 'MEASURE'
, NULL) ATTRIBUTE_TYPE
, MEASURE.ELEMENTID ATTRIBUTE_ID
, MEASURE.NAME ATTRIBUTE_NAME
, MEASURE.LONGNAME ATTRIBUTE_LONG_NAME
, DECODE(UK.NAME
, NULL
, NULL
, 'UK')
, UK.ELEMENTID UK_ID
, UK.NAME UK_NAME
FROM CMPWBCUBE_V FACT
, CMPWBCUBEATTRIBUTE_V MEASURE
, CMPUNIQUEKEY_V UK
, CMPWBITEMSETUSAGE_V UKEYCOL
WHERE FACT.ELEMENTID = MEASURE.OWNINGRELATION
AND UKEYCOL.ATTRIBUTE (+) = MEASURE.ELEMENTID
AND UK.ELEMENTID (+) = UKEYCOL.ITEMSET UNION SELECT FACT.ELEMENTID FACT_ID
, FACT.NAME FACT_NAME
, NULL ATTRIBUTE_TYPE
, UCOL.ELEMENTID ATTRIBUTE_ID
, UCOL.NAME ATTRIBUTE_NAME
, UCOL.LONGNAME ATTRIBUTE_LONG_NAME
, 'UK'
, UK.ELEMENTID UK_ID
, UK.NAME UK_NAME
FROM CMPWBCUBE_V FACT
, CMPUNIQUEKEY_V UK
, CMPWBITEMSETUSAGE_V UKEYCOL
, CMPRELATIONALDBITEM_V UCOL
WHERE FACT.ELEMENTID = UK.OWNINGRELATION
AND UK.ELEMENTID = UKEYCOL.ITEMSET
AND UKEYCOL.ATTRIBUTE = UCOL.ELEMENTID UNION SELECT FACT.ELEMENTID FACT_ID
, FACT.NAME FACT_NAME
, NULL ATTRIBUTE_TYPE
, FCOL.ELEMENTID ATTRIBUTE_ID
, FCOL.NAME ATTRIBUTE_NAME
, FCOL.LONGNAME ATTRIBUTE_LONG_NAME
, 'FK'
, FK.ELEMENTID FK_ID
, FK.NAME FK_NAME
FROM CMPWBCUBE_V FACT
, CMPFOREIGNKEY_V FK
, CMPWBITEMSETUSAGE_V FKEYCOL
, CMPRELATIONALDBITEM_V FCOL
WHERE FACT.ELEMENTID = FK.OWNINGRELATION
AND FK.ELEMENTID = FKEYCOL.ITEMSET
AND FKEYCOL.ATTRIBUTE = FCOL.ELEMENTID WITH READ ONLY
Columns
Name |
FACT_ID |
FACT_NAME |
ATTRIBUTE_TYPE |
ATTRIBUTE_ID |
ATTRIBUTE_NAME |
ATTRIBUTE_LONGNAME |
KEY_TYPE |
KEY_ID |
KEY_NAME |