Re: SMI query for Index column names

Hey, you all, there's got to be an easier way. I know the question is how to using the SMI. I gave up in frustration with the SMI way and just did a dbschema and applied sed and awk to the result. The only gotcha first time was long concatenated indexes that caused line wraps. Yours, Nick At 10:57 AM 9/24/98 -0700, Jonathan Leffler wrote: >On Thu, 24 Sep 1998, Art S. Kagel wrote: >> Douglas Agnew wrote: >> > the solution would appear to be a union across all the part1--part16 >> > where the value is not zero. >> [SNIP] >> > > Let me put in this way. If you use >> > > dbaccess->database->tables->indexes >> > > it shows you all the indexcolumn names for all the indexes. >> > > How can i get this from SMI query. >> >> Actually you are querying the database's system catalog tables not SMI, >> but I won't nit pick ;-) I just want to add that if any column is >> declared as descending in the index (less common in 7.3x since 7.3 can >> use ascending indexes for descending searches) the column number in the >> partxx will be negated so the filter should be: c.colno = abs(x.part1) >> etc. > >Given a tabid as pre-requisite information, this query produces a set of >column names in a single row of data for each index for an OnLine table >(max 16 parts). The simplication to SE with 8 parts is trivial. Note >that this loses the information about whether the columns are ascending >or descending. > >SELECT t0.tabid, t0.idxtype, t0.owner, t0.idxname, > c01.colname col01, c02.colname col02, c03.colname col03, > c04.colname col04, c05.colname col05, c06.colname col06, > c07.colname col07, c08.colname col08, c09.colname col09, > c10.colname col10, c11.colname col11, c12.colname col12, > c13.colname col13, c14.colname col14, c15.colname col15, > c16.colname col16 > FROM "informix".SysIndexes t0, > "informix".SysColumns c01, > OUTER "informix".SysColumns c02, > OUTER "informix".SysColumns c03, > OUTER "informix".SysColumns c04, > OUTER "informix".SysColumns c05, > OUTER "informix".SysColumns c06, > OUTER "informix".SysColumns c07, > OUTER "informix".SysColumns c08, > OUTER "informix".SysColumns c09, > OUTER "informix".SysColumns c10, > OUTER "informix".SysColumns c11, > OUTER "informix".SysColumns c12, > OUTER "informix".SysColumns c13, > OUTER "informix".SysColumns c14, > OUTER "informix".SysColumns c15, > OUTER "informix".SysColumns c16 > WHERE t0.tabid = <<tabid>> > AND ABS(t0.part1) = c01.colno AND t0.tabid = c01.tabid > AND ABS(t0.part2) = c02.colno AND t0.tabid = c02.tabid > AND ABS(t0.part3) = c03.colno AND t0.tabid = c03.tabid > AND ABS(t0.part4) = c04.colno AND t0.tabid = c04.tabid > AND ABS(t0.part5) = c05.colno AND t0.tabid = c05.tabid > AND ABS(t0.part6) = c06.colno AND t0.tabid = c06.tabid > AND ABS(t0.part7) = c07.colno AND t0.tabid = c07.tabid > AND ABS(t0.part8) = c08.colno AND t0.tabid = c08.tabid > AND ABS(t0.part9) = c09.colno AND t0.tabid = c09.tabid > AND ABS(t0.part10) = c10.colno AND t0.tabid = c10.tabid > AND ABS(t0.part11) = c11.colno AND t0.tabid = c11.tabid > AND ABS(t0.part12) = c12.colno AND t0.tabid = c12.tabid > AND ABS(t0.part13) = c13.colno AND t0.tabid = c13.tabid > AND ABS(t0.part14) = c14.colno AND t0.tabid = c14.tabid > AND ABS(t0.part15) = c15.colno AND t0.tabid = c15.tabid > AND ABS(t0.part16) = c16.colno AND t0.tabid = c16.tabid;> >If you want to list these as a series of rows then you use a 16-way >UNION as suggested. > >Yours, >Jonathan Leffler (jleffler@informix.com) #include <witticism.h> >Guardian of DBD::Informix v0.60 -- http://www.perl.com/CPAN >Informix IDN for D4GL & Linux -- http://www.informix.com/idn > > ********************* Nick Nobbe NLS/BPH Library of Congress nnob@loc.gov