select attr.owner owner,
attr.dimension_name dimension_name,
(case when attr.hidden = 'N'
then attr.hierarchy_name else null end) hierarchy_name,
attr.attribute_name attribute_name,
attr.lvl_attribute_name lvl_attribute_name,
attr.level_name level_name,
attr.table_owner table_owner,
attr.table_name table_name,
attr.column_name column_name,
attr.dtype dtype,
attr.data_length data_length,
attr.data_precision data_precision,
(case when attr.dtype = 'NUMBER' then 0
when attr.dtype = 'DOUBLE' then 5
when attr.dtype = 'FLOAT' then 4
when attr.dtype = 'DATE' then 7
when attr.dtype = 'LONG' then 3
else 1 end) olap_api_data_type
from (
select
d.owner owner,
d.name dimension_name,
h.name hierarchy_name,
da.name attribute_name,
la.name lvl_attribute_name,
l.name level_name,
u.username table_owner,
o.name table_name,
col.name column_name,
h.hidden hidden,
decode(col.type#, 1, decode(col.charsetform, 2, 'NVARCHAR2', 'VARCHAR2'),
2, decode(col.scale, null,
decode(col.precision#, null, 'NUMBER', 'FLOAT'),
'NUMBER'),
8, 'LONG',
9, decode(col.charsetform, 2, 'NCHAR VARYING', 'VARCHAR'),
12, 'DATE', 23, 'RAW', 24, 'LONG RAW',
69, 'ROWID',
96, decode(col.charsetform, 2, 'NCHAR', 'CHAR'),
105, 'MLSLABEL',
106, 'MLSLABEL',
112, decode(col.charsetform, 2, 'NCLOB', 'CLOB'),
113, 'BLOB', 114, 'BFILE', 115, 'CFILE',
178, 'TIME(' ||col.spare1|| ')',
179, 'TIME(' ||col.spare1|| ')' || ' WITH TIME ZONE',
180, 'TIMESTAMP(' ||col.spare1|| ')',
181, 'TIMESTAMP(' ||col.spare1|| ')' || ' WITH TIME ZONE',
182, 'INTERVAL YEAR(' ||col.spare2||') TO MONTH',
183, 'INTERVAL DAY(' ||col.spare2||') TO SECOND(' ||
col.spare1 || ')',
208, 'UROWID',
'UNDEFINED') dtype
, decode(col.length, null, 0, col.length) data_length
, decode(col.precision#, null, 0, col.precision#) data_precision
from olapsys.CwM2$Dimension d,
olapsys.cwm2$dimensionattribute da,
olapsys.cwm2$level l,
olapsys.CwM2$LevelAttribute la,
olapsys.CwM2$LevelAttributeMap lam,
olapsys.CwM2$Hierarchy h,
olapsys.CwM2$HierLevelRel hlr,
dba_users u,
sys.obj$ o,
sys.col$ col
where d.irid = da.dimension_irid and
la.dimattr_irid = da.irid and
la.level_irid = l.irid and
la.irid = lam.levelattr_irid and
lam.hierlvlrel_irid = hlr.irid and
hlr.hierarchy_irid = h.irid and
lam.Table_ID = o.obj# and
lam.Column_ID = col.col# and
o.obj# = col.obj# and
o.owner# = u.user_id) attr
with read only
SELECT ATTR.OWNER OWNER
,
ATTR.DIMENSION_NAME DIMENSION_NAME
,
(CASE WHEN ATTR.HIDDEN = 'N'
THEN ATTR.HIERARCHY_NAME ELSE NULL END) HIERARCHY_NAME
,
ATTR.ATTRIBUTE_NAME ATTRIBUTE_NAME
,
ATTR.LVL_ATTRIBUTE_NAME LVL_ATTRIBUTE_NAME
,
ATTR.LEVEL_NAME LEVEL_NAME
,
ATTR.TABLE_OWNER TABLE_OWNER
,
ATTR.TABLE_NAME TABLE_NAME
,
ATTR.COLUMN_NAME COLUMN_NAME
,
ATTR.DTYPE DTYPE
,
ATTR.DATA_LENGTH DATA_LENGTH
,
ATTR.DATA_PRECISION DATA_PRECISION
,
(CASE WHEN ATTR.DTYPE = 'NUMBER' THEN 0
WHEN ATTR.DTYPE = 'DOUBLE' THEN 5
WHEN ATTR.DTYPE = 'FLOAT' THEN 4
WHEN ATTR.DTYPE = 'DATE' THEN 7
WHEN ATTR.DTYPE = 'LONG' THEN 3
ELSE 1 END) OLAP_API_DATA_TYPE
FROM (
SELECT
D.OWNER OWNER
,
D.NAME DIMENSION_NAME
,
H.NAME HIERARCHY_NAME
,
DA.NAME ATTRIBUTE_NAME
,
LA.NAME LVL_ATTRIBUTE_NAME
,
L.NAME LEVEL_NAME
,
U.USERNAME TABLE_OWNER
,
O.NAME TABLE_NAME
,
COL.NAME COLUMN_NAME
,
H.HIDDEN HIDDEN
,
DECODE(COL.TYPE#
, 1
, DECODE(COL.CHARSETFORM
, 2
, 'NVARCHAR2'
, 'VARCHAR2')
,
2
, DECODE(COL.SCALE
, NULL
,
DECODE(COL.PRECISION#
, NULL
, 'NUMBER'
, 'FLOAT')
,
'NUMBER')
,
8
, 'LONG'
,
9
, DECODE(COL.CHARSETFORM
, 2
, 'NCHAR VARYING'
, 'VARCHAR')
,
12
, 'DATE'
, 23
, 'RAW'
, 24
, 'LONG RAW'
,
69
, 'ROWID'
,
96
, DECODE(COL.CHARSETFORM
, 2
, 'NCHAR'
, 'CHAR')
,
105
, 'MLSLABEL'
,
106
, 'MLSLABEL'
,
112
, DECODE(COL.CHARSETFORM
, 2
, 'NCLOB'
, 'CLOB')
,
113
, 'BLOB'
, 114
, 'BFILE'
, 115
, 'CFILE'
,
178
, 'TIME(' ||COL.SPARE1|| ')'
,
179
, 'TIME(' ||COL.SPARE1|| ')' || ' WITH TIME ZONE'
,
180
, 'TIMESTAMP(' ||COL.SPARE1|| ')'
,
181
, 'TIMESTAMP(' ||COL.SPARE1|| ')' || ' WITH TIME ZONE'
,
182
, 'INTERVAL YEAR(' ||COL.SPARE2||') TO MONTH'
,
183
, 'INTERVAL DAY(' ||COL.SPARE2||') TO SECOND(' ||
COL.SPARE1 || ')'
,
208
, 'UROWID'
,
'UNDEFINED') DTYPE
, DECODE(COL.LENGTH
, NULL
, 0
, COL.LENGTH) DATA_LENGTH
, DECODE(COL.PRECISION#
, NULL
, 0
, COL.PRECISION#) DATA_PRECISION
FROM OLAPSYS.CWM2$DIMENSION D
,
OLAPSYS.CWM2$DIMENSIONATTRIBUTE DA
,
OLAPSYS.CWM2$LEVEL L
,
OLAPSYS.CWM2$LEVELATTRIBUTE LA
,
OLAPSYS.CWM2$LEVELATTRIBUTEMAP LAM
,
OLAPSYS.CWM2$HIERARCHY H
,
OLAPSYS.CWM2$HIERLEVELREL HLR
,
DBA_USERS U
,
SYS.OBJ$ O
,
SYS.COL$ COL
WHERE D.IRID = DA.DIMENSION_IRID AND
LA.DIMATTR_IRID = DA.IRID AND
LA.LEVEL_IRID = L.IRID AND
LA.IRID = LAM.LEVELATTR_IRID AND
LAM.HIERLVLREL_IRID = HLR.IRID AND
HLR.HIERARCHY_IRID = H.IRID AND
LAM.TABLE_ID = O.OBJ# AND
LAM.COLUMN_ID = COL.COL# AND
O.OBJ# = COL.OBJ# AND
O.OWNER# = U.USER_ID) ATTR
WITH READ ONLY
|
|
|