Request system...
Hi!
just tested this request ... very good for Informix!
Someone can explain to me what haps ?
My version :
INFORMIX-Universal Server Version 9.14.UC4
OS : Solaris
select sc.colname, si.idxname, si.clustered, si.idxtype, st.tabname,
st.owner from informix.systables st, informix.sysindexes si,
informix.syscolumns sc where (st.tabname like 'r_est_trad_tr' or
st.tabname like 'r_est_trad_tr') and st.tabid = si.tabid and sc.tabid =
st.tabid and st.owner like 'informix' and sc.colno in (si.part1,si.part2,
si.part3, si.part4, si.part5, si.part6, si.part7, si.part8, si.part9,
si.part10, si.part11, si.part12, si.part13, si.part14, si.part15,
si.part16) order by si.idxname;
/Franck
Returns information on columns that are part of the indexes of the table
"r_est_trad_tr"
Rudy
Franck Andriano wrote:
> Hi!
>
> just tested this request ... very good for Informix!
> Someone can explain to me what haps ?
>
> My version :
> INFORMIX-Universal Server Version 9.14.UC4
> OS : Solaris
>
> select sc.colname, si.idxname, si.clustered, si.idxtype, st.tabname,
> st.owner from informix.systables st, informix.sysindexes si,
> informix.syscolumns sc where (st.tabname like 'r_est_trad_tr' or
> st.tabname like 'r_est_trad_tr') and st.tabid = si.tabid and sc.tabid =
> st.tabid and st.owner like 'informix' and sc.colno in (si.part1,si.part2,
> si.part3, si.part4, si.part5, si.part6, si.part7, si.part8, si.part9,
> si.part10, si.part11, si.part12, si.part13, si.part14, si.part15,
> si.part16) order by si.idxname;>
> /Franck
Reports the columns of the two listed tables which are included in indexes.
HOWEVER, it is not correct. If any of the indexed columns are DESCending
in the key they will not be found. Each of the 'si.part?' entries should
be ABS(si.part?) as the colno of a descending column is recorded in
sysindexes and colno * -1.
Art S. Kagel
Franck Andriano wrote:
>
> Hi!
>
> just tested this request ... very good for Informix!
> Someone can explain to me what haps ?
>
> My version :
> INFORMIX-Universal Server Version 9.14.UC4
> OS : Solaris
>
> select sc.colname, si.idxname, si.clustered, si.idxtype, st.tabname,
> st.owner from informix.systables st, informix.sysindexes si,
> informix.syscolumns sc where (st.tabname like 'r_est_trad_tr' or
> st.tabname like 'r_est_trad_tr') and st.tabid = si.tabid and sc.tabid =
> st.tabid and st.owner like 'informix' and sc.colno in (si.part1,si.part2,
> si.part3, si.part4, si.part5, si.part6, si.part7, si.part8, si.part9,
> si.part10, si.part11, si.part12, si.part13, si.part14, si.part15,
> si.part16) order by si.idxname;>
> /Franck