Re: SMI query for Index column names

Guys, You don't have to be afraid to use OR in SQL (instead of unions and outer joins) ... try: select x.idxname, c.colname from syscolumns c, systables t, sysindexes x where t.tabname = 'adm_translation' and t.owner = 'DBO' and x.tabid = t.tabid and c.tabid = t.tabid and ( c.colno = x.part1 or c.colno = x.part2 or c.colno = x.part3 or c.colno = x.part4 or c.colno = x.part5 or c.colno = x.part6 or c.colno = x.part7 or c.colno = x.part8 or c.colno = x.part9 or c.colno = x.part10 or c.colno = x.part11 or c.colno = x.part12 or c.colno = x.part13 or c.colno = x.part14 or c.colno = x.part15 or c.colno = x.part16 ) or the same with the ABS(x.part*) variety Gabor 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