[Home] [Help]
View: EDW_DIM_ATTRIBUTES_MD_V
View Text
SELECT DIM.ELEMENTID
, DIM.NAME
, ATTR.ELEMENTID
, ATTR.NAME
, ATTR.LONGNAME
, DIMLVL.NAME
, SUBSTR(ATTR.NAME
, 1
, INSTR(ATTR.NAME
, '_'
, 1
, 1)-1)
, TO_NUMBER(NULL)
, NULL
FROM CMPWBDIMENSION_V DIM
, CMPRELATIONALDBITEM_V ATTR
, CMPLEVEL_V DIMLVL
WHERE ATTR.OWNINGRELATION = DIM.ELEMENTID
AND DIMLVL.DIMENSION = DIM.ELEMENTID
AND DIMLVL.PREFIX = SUBSTR(ATTR.NAME
, 1
, INSTR(ATTR.NAME
, '_'
, 1
, 1)-1)
AND NOT EXISTS( SELECT 1
FROM CMPWBITEMSETUSAGE_V KEYCOL
, CMPUNIQUEKEY_V UK
WHERE ATTR.ELEMENTID = KEYCOL.ATTRIBUTE
AND KEYCOL.ITEMSET = UK.ELEMENTID ) UNION SELECT DIM.ELEMENTID
, DIM.NAME
, ATTR.ELEMENTID
, ATTR.NAME
, ATTR.LONGNAME
, DIMLVL.NAME
, SUBSTR(ATTR.NAME
, 1
, INSTR(ATTR.NAME
, '_'
, 1
, 1)-1)
, UK.ELEMENTID
, UK.NAME
FROM CMPWBDIMENSION_V DIM
, CMPRELATIONALDBITEM_V ATTR
, CMPWBITEMSETUSAGE_V KEYCOL
, CMPUNIQUEKEY_V UK
, CMPLEVEL_V DIMLVL
WHERE DIM.ELEMENTID = ATTR.OWNINGRELATION
AND DIMLVL.DIMENSION = DIM.ELEMENTID
AND DIMLVL.PREFIX = SUBSTR(ATTR.NAME
, 1
, INSTR(ATTR.NAME
, '_'
, 1
, 1)-1)
AND ATTR.ELEMENTID = KEYCOL.ATTRIBUTE
AND KEYCOL.ITEMSET = UK.ELEMENTID WITH READ ONLY
Columns
Name |
DIM_ID |
DIM_NAME |
ATTRIBUTE_ID |
ATTRIBUTE_NAME |
ATTRIBUTE_LONGNAME |
ATTRIBUTE_SOURCE_LEVEL |
ATTRIBUTE_SOURCE_LEVEL_PREFIX |
UK_ID |
UK_NAME |