[Home] [Help]
View: EDW_LEVEL_TABLE_ATTS_MD_V
View Text
SELECT DIM.ELEMENTID
, DIM.NAME
, LVLTBL.ELEMENTID
, LVLTBL.NAME
, LVL.PREFIX
, COL.ELEMENTID
, COL.NAME
, COL.LONGNAME
, NULL
, TO_NUMBER(NULL)
, NULL
, LVL.ELEMENTID
, LVL.NAME
, LVL.LONGNAME
FROM CMPWBDIMENSION_V DIM
, CMPLEVEL_V LVL
, CMPRELATION_V LVLTBL
, CMPRELATIONALDBITEM_V COL
WHERE DIM.ELEMENTID = LVL.DIMENSION
AND LVLTBL.CLASSNAME = 'CMPWBTABLE'
AND LVL.NAME || '_LTC' = LVLTBL.NAME
AND LVLTBL.ELEMENTID = COL.OWNINGRELATION
AND NOT EXISTS ( SELECT 1
FROM CMPWBITEMSETUSAGE_V KEYCOL
WHERE KEYCOL.ATTRIBUTE = COL.ELEMENTID ) UNION SELECT DIM.ELEMENTID
, DIM.NAME
, LVLTBL.ELEMENTID
, LVLTBL.NAME
, LVL.PREFIX
, UCOL.ELEMENTID
, UCOL.NAME
, UCOL.LONGNAME
, 'UK'
, UK.ELEMENTID
, UK.NAME
, LVL.ELEMENTID
, LVL.NAME
, LVL.LONGNAME
FROM CMPWBDIMENSION_V DIM
, CMPLEVEL_V LVL
, CMPRELATION_V LVLTBL
, CMPRELATIONALDBITEM_V UCOL
, CMPUNIQUEKEY_V UK
, CMPWBITEMSETUSAGE_V UKEYCOL
WHERE DIM.ELEMENTID = LVL.DIMENSION
AND LVLTBL.CLASSNAME = 'CMPWBTABLE'
AND LVL.NAME || '_LTC' = LVLTBL.NAME
AND LVLTBL.ELEMENTID = UCOL.OWNINGRELATION
AND LVLTBL.ELEMENTID = UK.OWNINGRELATION
AND UK.ELEMENTID = UKEYCOL.ITEMSET
AND UKEYCOL.ATTRIBUTE = UCOL.ELEMENTID UNION SELECT DIM.ELEMENTID
, DIM.NAME
, LVLTBL.ELEMENTID
, LVLTBL.NAME
, LVL.PREFIX
, FCOL.ELEMENTID
, FCOL.NAME
, FCOL.LONGNAME
, 'FK'
, FK.ELEMENTID
, FK.NAME
, LVL.ELEMENTID
, LVL.NAME
, LVL.LONGNAME
FROM CMPWBDIMENSION_V DIM
, CMPLEVEL_V LVL
, CMPRELATION_V LVLTBL
, CMPRELATIONALDBITEM_V FCOL
, CMPFOREIGNKEY_V FK
, CMPWBITEMSETUSAGE_V FKEYCOL
WHERE DIM.ELEMENTID = LVL.DIMENSION
AND LVLTBL.CLASSNAME = 'CMPWBTABLE'
AND LVL.NAME || '_LTC' = LVLTBL.NAME
AND LVLTBL.ELEMENTID = FCOL.OWNINGRELATION
AND LVLTBL.ELEMENTID = FK.OWNINGRELATION
AND FK.ELEMENTID = FKEYCOL.ITEMSET
AND FKEYCOL.ATTRIBUTE = FCOL.ELEMENTID WITH READ ONLY
Columns
Name |
DIM_ID |
DIM_NAME |
LEVEL_TABLE_ID |
LEVEL_TABLE_NAME |
LEVEL_PREFIX |
LEVEL_TABLE_COL_ID |
LEVEL_TABLE_COL_NAME |
LEVEL_TABLE_COL_LONG_NAME |
KEY_TYPE |
KEY_ID |
KEY_NAME |
LEVEL_ID |
LEVEL_NAME |
LEVEL_LONG_NAME |