Re: Indexing troubles

Here is the relevant schema: create table journal ( jrnlnmbr serial, j_date date, casenmbr integer, casecode char(11), peopnmbr integer, peopcode char(17), evntnmbr integer, evntcode char(11), j_descr char(45), j_rate decimal(8,3), j_quant decimal(6,2), j_status char(1), j_time datetime hour to minute ); create unique index journal_idx on journal (jrnlnmbr); create cluster index ix_journal on journal (j_date,casenmbr, evntnmbr,j_status); create index ix_j_cscode on journal (casecode); create index ix_j_cs on journal (casenmbr); create index ix_j_evcode on journal (evntcode); create index ix_j_evsys on journal (evntnmbr); create index ix_j_ppsys on journal (peopnmbr); create index ix_j_dte on journal (j_date desc); create index ix_j_ppcode on journal (peopcode);alter table .journal add constraint primary key (jrnlnmbr) constraint journal_key ; It is the second index (ix_journal) that fails, and it is the field j_status that is being reported as already indexed. My procedure in upgrading to SE 7.23 has been to: 1) create the table; 2) load the data; 3) build the idexes; 4) create the procedures; 5) create triggers; and, 6) update statistics. This schema and set of indexes has been stable for several years, from probably SE 4.1. I've been upgrading several sites and only one seems to be bothered by this "hidden" constraint. -- --------------------------------------------------------------------- Scott Holmes http://www.pacificnet.net/~sholmes sholmes@pacificnet.net Independent Programmer/Analyst Passport 4GL HTML Composer Informix 4GL, SQL --------------------------------------------------------------------- There are more things in heaven and earth, Horatio, than are dreamt of in your philosophy ---------------------------------------------------------------------