Re: Resequencing a table

david@inform.co.nz (David Paulo) wrote: :We have a to do list stored in a table much simplified as follows: : : :Create Table TASKS :(taskcode CHAR(6) :,stepno DECIMAL(9,0) :,description char(32) :) : :We can create any indexes or temporary tables that may be needed. : :taskcode is a code for a particular task. :stepno identifies the individual steps to be done in the task. :stepno is programatically generated with increments of 10 as the user inserts :the steps. :In order to allow the user to rearrange the steps he is allowed to change the stepno. : :Sooner or later we want to be able to renumber the steps, :so as to get back to increments of 10. : :The algorithm for doing this (that we thought of) is: : start at the highest numbered step and in descenging order change stepno : to 999999999,999999998 etc until all steps are renumbered. : start at the lowest numbered step and in increasing order change stepno : to 10,20,30 etc until all steps are renumbered. : :It seems to me that this should be doable in SPL, but I can't figure out how :because foreach does not allow an order by clause and when doing the : renumbering we have to kow that the rows are coming in ordered. See the Informix SQL Syntax manual for OnLine 7.22 for NT (downloaded from www.informix.com) page 2-25. The foreach can certainly have an order by. However you can not say: foreach cursorname for select... order by... update ... where current of cursorname because Informix doesn't allow order by in a for update cursor. (A bad day for the designers som time long ago lead to this :( Use: foreach select ... order by... update ... where primary_key = ... instead. That should work fine. :If it can't be done as a stored procedure we will impliment it in 4gl :but that seems to be less efficient. Probably not that big of a difference. :Any pointers welcome, and if the response is RTFM please give a page reference :'cause I can't see it. Have done, but granted it's not explained on these pages. It was explained (by Jonathan Leffler I think) some time ago here in c.d.i. so this is the place to learn things. :If our algorithm sucks a pointer to a better approach is also welcome. Well, it doesn't suck, but it isn't nice either. If there aren't to many steps for each task I would think of using a smallint for the stepno and maintain them in order at all times. I would probably read all steps into memory while the user changed them, and update only the steps after the change if he added or removed steps. This may not work for you though. Particularly not if there may be more than a few hundred steps or they are changed very often by very many users. It may also be a complete misunderstanding of what you are actually doing. Nils.Myklebust@idg.no NM Data AS, P.O.Box 9090 Gronland, N-0133 Oslo, Norway My opinions are those of my company The Informix FAQ is at http://www.iiug.org