a) take a look at http://www.cvalde.net/document/mysteriousDbKeyI.htm.
Cursors may be an alternative, but I simply do not know enough about
them to tell you whether that is possible or not

b) your error will simply disappear if you implement what Claudio
writes in my answer to a, but in case you are curious about why you
have gaps:

You do 'for select date' and not 'for select distinct date'. This
means that if more than one record has the same date, then you will
get e.g.

26.08.2004
27.08.2004
27.08.2004
28.08.2004

Your update does update all record on the current date, meaning that
records from 27.08.2004 (accidentally, that is today ;o) are updated
twice, with the second execution overwriting the first. Hence, the
gaps.

If each group contains a lot of records, then you may find that an
index on (sifkor,god,orgj,date, PK) is better than simply (sifkor,god,
orgj,date). Try for yourself.

HTH,
Set

--- In firebird-support@yahoogroups.com, "ivoras" wrote:
> I need to "renumber" records in a table according to some "grouping"
> fields, and I though of doing it with this stored procedure:
>
> ----------
> CREATE PROCEDURE RENUM (
> SIFKOR VARCHAR(10),
> GOD INTEGER,
> ORGJ SMALLINT,
> START INTEGER)
> AS
> DECLARE VARIABLE D DATE;
> DECLARE VARIABLE N INTEGER;
> begin
> select gen_id(tmp$gen, 0) from rdb$database into :n;
> for
> select date
> from tkm
> where sifkor=:sifkor and god=:god and orgj=:orgj
> order by date
> into :d
> do
> update tkm
> set rbtk=gen_id(tmp$gen, 1)-:n+:start
> where sifkor=:sifkor and god=:god
> and orgj=:orgj and date=:d;
> suspend;
> end
>
> --------------
> The goal is to "group" records by (sifkor,god,orgj), and in each
> group renumber the records (field "rbtk") ordered by date and
> starting at :start (so the earliest record gets rbtk=:start,
> the next one :start+1, etc.). The above procedure renumbers one
> group.
>
> I have two problems with the above code:
> a) It works slowly (despite having index on (sifkor,god,orgj,date);
> I think I should use cursors to do the updating, but are they
> allowed inside a procedure? If true, can somebody write an
> example of how they're used?)
>
> b) (much more serious) It doesn't work :( Some numbers in the
sequence
> are strangely missing. For example, first few records in a group
> get numbered correctly, then there's a gap of about 2-5 numbers,
> then a sequence, etc. It's important that the numbers are in
> sequential order, and I don't see any errors in my procedure :(