Svenson's Bulletin Board

[craig]

with t(ordinal,string,tbcreator,tbname,indexname) as
(
select 1,colnames,tbcreator,tbname,name as indexname from sysibm.sysindexes
union all
select ordinal+1
,case locate('+',string,2,octets)when 0 then null
else substr(string,locate('+',string,2,octets))
end as string
,tbcreator,tbname,indexname
from t
where string is not null
and ordinal < 100-- this line added to loose the 437 warning
),
tcols as
(
select ordinal
,case locate('+',string,2,octets)when 0 then
substr(string,2)
else
substr(string,2,locate('+',string,2,octets)-2)
end as column_name
,tbcreator,tbname,indexname
from t
where string is not null
),
sc as (
select tbcreator,tbname,rtrim(name) as name ,length
from sysibm.syscolumns
)
select sc.tbcreator
,sc.tbname
,si.indexname
,si.ordinal
,si.column_name as si_colname
,sc.length as sc_length
from tcols si,sc
where si.tbcreator=sc.tbcreator
and si.tbname=sc.tbname
and sc.name =si.column_name
order by sc.tbcreator
,sc.tbname
,si.indexname
,si.ordinal
;