Re: Resequencing a table
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.
}
Try this algorithm/code snippets:
- Using 4GL, setup a cursor, row_curs, as follows:
declare row_curs cursor for
select stepno from tasks
order by stepno
for update
- Later, in the function to update the stepno, use this code:
define new_step integer -- to allow for large values of stepno
...
let new_step = 0
foreach row_curs into dummy_step
let new_step = new_step + 10
update tasks
set stepno = new_step
where current of row_curs
end foreach
This presumes that you do not have duplicate stepno values in the
database. Sorry I don't have much experience with SPL.
Happy coding.
} 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.
}
} If it can't be done as a stored procedure we will impliment it in 4gl
} but that seems to be less efficient.
}
} Any pointers welcome, and if the response is RTFM please give a page
} reference 'cause I can't see it.
}
} If our algorithm sucks a pointer to a better approach is also welcome.
}
} TIA
}
} ----------------------------------------------------------------------
} David Paulo, InForm Group Ltd.,PO Box 1444, Wellington, New Zealand.
} Ph: +64 4 472 0996 Fax: +64 4 473 2407 Email: david@inform.co.nz
} ----------------------------------------------------------------------
Best regards,
Nigel
--
+--------------------------------------------------------------------+
| Name : Edmund Nigel Gall Address: 9A Stanmore Avenue |
| Title : Account Executive Port of Spain |
| Company : Stratis Caribbean Ltd. Trinidad & Tobago, W.I. |
| |
| Contact Information: |
| Office Personal |
| Voice : (809) 623 6530/1 (809) 662 6611 |
| Fax : (809) 623 8370 (809) 662 6611 |
| Email : ngall@stratis-caribbean.com nigelg@trinidad.net |
+----------------- http://www.stratis-caribbean.com -----------------+