Hello,

If you are interested in a sequence like a chequebook, where
a cheque gets either written ,voided or voided after having
been written, the following procedure and small table worked
great for me. I converted it from an Invoice program I wrote
to manage a small computer store for 12 years with 3 terminals
on a small network. Don't own the store anymore :)

This procedure keeps tracks of various Ticket_Types each having
their own unique sequence, their initial and current statii kept
track of in the little table.

I call it with POSTIT = 'F' while generating a Invoice Ticker
to get a number which might be close to the final result and
then call it again with POSTIT = 'T' prior to post & printing
the final result. Prior to post & printing, I can rollback as
much as I want.

Best Regards
Hans

---
ALTER PROCEDURE GET_NEXT_TICKETNO ( TICKET_TYPE CHAR( 2 )
, POSTIT CHAR( 1 ) )

RETURNS ( TICKET_NO INTEGER )
AS
DECLARE VARIABLE STARTNO INTEGER;
DECLARE VARIABLE CURNO INTEGER;

BEGIN

SELECT
STARTNO,
CURNO
FROM TICKET_MASTER
WHERE TICKET_TYPE = :TICKET_TYPE
INTO
:STARTNO,
:CURNO;

IF (STARTNO IS Null) THEN
BEGIN
STARTNO = 1;

UPDATE TICKET_MASTER
SET STARTNO = :STARTNO
WHERE TICKET_TYPE = :TICKET_TYPE;
END

IF (CURNO IS Null) THEN
CURNO = 0;

TICKET_NO = CURNO + 1;

IF (TICKET_NO < STARTNO) THEN
TICKET_NO = STARTNO;

IF (EXISTS (SELECT TICKET_UNIQUENUM FROM TICKETS
WHERE TICKET_NO = :TICKET_NO )) THEN
BEGIN
TICKET_NO = TICKET_NO - 1;

WHILE (EXISTS (SELECT TICKET_UNIQUENUM FROM TICKETS
WHERE TICKET_NO = :TICKET_NO )) DO
TICKET_NO = TICKET_NO + 1;

END

IF (POSTIT = 'T') THEN
UPDATE TICKET_MASTER
SET CURNO = :TICKET_NO
WHERE TICKET_TYPE = :TICKET_TYPE;

SUSPEND;
END
---
CREATE TABLE TICKET_MASTER (
TICKET_TYPE CHAR( 2 )
, STARTNO INTEGER
, CURNO INTEGER
, PRIMARY KEY TICKET_TYPE)
---

rpsb@... wrote:

>
> Hi!
>
> I'm using IBO 4.2 Ed and programming in Delphi
> 6.0.
> I would like to know how can I implement a
> sequence in a table, using IBO?
> I will describe what I would like to do:
>
> I have a table, named "FACTURACAO", with a PK
> "ID_FACT". In my application, when a user inserts
> a new row, I want to have de next ID. e.g. If
> MAX(ID_FACT)=3 -> new ID_FACT=4
> I could have multiple users inserting rows at
> same time...
>
> How can I prevent that the next number is given
> only to one person? There are any way to lock the
> record when I'm reading the generator? If a user
> cancel a insert the sequence is rolled back?
>
> I see the IBO help, and I had read something
> about this, but I don´t understand the explain.
>
> So, I hope you can help me...
>
> Thanks, for all
>
> Rui Batista
>
> --
> Crie o seu Email Grátis no Clix em
> http://registo.clix.pt/
>
>
>
> Your use of Yahoo! Groups is subject to http://docs.yahoo.com/info/terms/