select idx_name isc_idx_name,
max(sec_type) isc_sec_type,
max(nvl(decode(oatid, 240102, sec_name), '')) isc_sec_name,
max(nvl(decode(oatid, 240103, sec_name), '')) isc_sec_tag,
max(nvl(decode(oatid, 240105,
decode(sec_name, 0, 'N', 1, 'Y')), '')) isc_sec_visible,
max(nvl(decode(oatid, 240107,
decode(sec_name, 2, 'NUMBER', 5, 'VARCHAR2',
12, 'DATE', 23, 'RAW', 96, 'CHAR',
null)), '')) isc_sec_datatype
from(
select c.idx_name idx_name, c.idx_owner#, a.ixv_value sec_name,
a.ixv_sub_group subg, a.ixv_sub_oat_id oatid,
decode(mod(b.ixv_oat_id, 100), 1, 'ZONE', 2, 'FIELD',
3, 'SPECIAL', 4, 'STOP', 5, 'ATTR', 7, 'MDATA',
8, 'COLUMN SDATA', 9, 'COLUMN MDATA', 10, 'SDATA',
11, 'NDATA', null) sec_type
from dr$index_value a, dr$index_value b, dr$index c
where
b.ixv_value = to_char(a.ixv_sub_group)
and b.ixv_sub_oat_id = 0
and b.ixv_sub_group = 0
and b.ixv_idx_id = c.idx_id
and c.idx_owner# = userenv('SCHEMAID')
)
group by subg, idx_name, idx_owner#
order by isc_idx_name, isc_sec_type
SELECT IDX_NAME ISC_IDX_NAME
,
MAX(SEC_TYPE) ISC_SEC_TYPE
,
MAX(NVL(DECODE(OATID
, 240102
, SEC_NAME)
, '')) ISC_SEC_NAME
,
MAX(NVL(DECODE(OATID
, 240103
, SEC_NAME)
, '')) ISC_SEC_TAG
,
MAX(NVL(DECODE(OATID
, 240105
,
DECODE(SEC_NAME
, 0
, 'N'
, 1
, 'Y'))
, '')) ISC_SEC_VISIBLE
,
MAX(NVL(DECODE(OATID
, 240107
,
DECODE(SEC_NAME
, 2
, 'NUMBER'
, 5
, 'VARCHAR2'
,
12
, 'DATE'
, 23
, 'RAW'
, 96
, 'CHAR'
,
NULL))
, '')) ISC_SEC_DATATYPE
FROM(
SELECT C.IDX_NAME IDX_NAME
, C.IDX_OWNER#
, A.IXV_VALUE SEC_NAME
,
A.IXV_SUB_GROUP SUBG
, A.IXV_SUB_OAT_ID OATID
,
DECODE(MOD(B.IXV_OAT_ID
, 100)
, 1
, 'ZONE'
, 2
, 'FIELD'
,
3
, 'SPECIAL'
, 4
, 'STOP'
, 5
, 'ATTR'
, 7
, 'MDATA'
,
8
, 'COLUMN SDATA'
, 9
, 'COLUMN MDATA'
, 10
, 'SDATA'
,
11
, 'NDATA'
, NULL) SEC_TYPE
FROM DR$INDEX_VALUE A
, DR$INDEX_VALUE B
, DR$INDEX C
WHERE
B.IXV_VALUE = TO_CHAR(A.IXV_SUB_GROUP)
AND B.IXV_SUB_OAT_ID = 0
AND B.IXV_SUB_GROUP = 0
AND B.IXV_IDX_ID = C.IDX_ID
AND C.IDX_OWNER# = USERENV('SCHEMAID')
)
GROUP BY SUBG
, IDX_NAME
, IDX_OWNER#
ORDER BY ISC_IDX_NAME
, ISC_SEC_TYPE
|
|
|