Perform record ordering

In respect of John Baker's perform problem: this seems sufficiently dirty it just might work ... This is sorta pseudo-SQL, BTW. Flames to the address at the very bottom. * create table apps_table ( col1 char(3), col2 char(77), time_order serial, del_marker char(1) ); col1 and col2 are what the application uses. Nulls must be allowed in the del_marker column. * create cluster index apps_index on apps_table (time_order, del_marker); Aside: is it the case that I can't index JUST on the serial col, because it's indexed already ? Not that it really matters ... * create view myview as select col1, col2, del_marker, time_order from apps_table where del_marker is null; You don't want the "with check" option on the view - see later. * Grant INSERT and UPDATE to all your normal users. Revoke DELETE from all your normal users. * Generate the Perform form on the view. You've now got an empty table and any rows added are given an ascending (serial) number in time_order. Your users cannot delete the rows once they've been added, on account of the privileges on the view/table, but they can mark them as deleted by putting a letter (people's choice = "d") in the del_marker field. When they do that, the row no longer makes the view criterion and won't appear next time they run a query. It'll presumably be in the current list if you've just changed it - I'm not absolutely sure what happens then :-) Anyhow, rows are added in physical = temporal order and should read back in that order. Overnight, you run a script to delete the rows that have been marked deleted and recluster the table (you're not running 24*7 here, are you ?). Something like this might swing it: : isql apps_db <<!! lock table apps_table in exclusive mode; delete from apps_table where del_marker is not null; alter index apps_index to cluster;!! Note that this should be run as someone who is allowed to delete rows, eg. informix. I'm overlooking minor (:-) details like transactions and logfile sizes, ok ? > QUESTIONS: > I thought indexes were merely supposed to speed the > search process and not affect the order in which rows are read. Yes, we've been cheating (and gotten caught !) > Unfortunately, the 4GL package is not > loaded on the machine in question (we have 5 machines and only one has 4GL; > the others have SQL). The users will have to get by with Perform for now. I was just wondering - are the machines binary compatible ? Don't answer "yes", people may be listening. > John Baker > USAISC - Lex Phone: (606) 293-3644 or 293-3743 > Lexington - Blue Grass Army Depot DSN: 745-3644 or 745-3743 > Lexington, KY 40511-5109 E-mail: jbaker@lexington-emh2.army.mil Good luck. Cheers - Tony. _______________________________________________________________________________ Tony Heskett th@bnr.co.uk |Voice: (+44) 279 429531 x 2637 BNR, London Road, Harlow, Essex, CM17 9NA |Fax: (+44) 279 454187