>If we are going the EXECUTE BLOCK approach, which I agree would be better.
>
>Then, the optimal logic would be:
>
> execute block returns (id integer) as
> declare variable Next_ID integer;
> begin
> ID = 1;
> FOR
> SELECT ID
> FROM Table
> ORDER BY ID
> INTO
> :Next_ID
> DO
> Next_ID = Next_ID - 1;
> WHILE (Next_ID >= ID) DO BEGIN
> SUSPEND;
> ID = ID +1;
> END
> ID = ID +1;
> end
Brilliant, Sean!
Though I would suggest some minor refinement:
EXECUTE BLOCK RETURNS (id integer) AS
DECLARE VARIABLE Next_ID integer;
BEGIN
ID = 1;
FOR
SELECT ID
FROM Table
ORDER BY ID
INTO
:Next_ID
DO
BEGIN
WHILE (Next_ID > ID) DO BEGIN
SUSPEND;
ID = ID +1;
END
ID = ID +1;
END
END
or even:
EXECUTE BLOCK RETURNS (id integer) AS
DECLARE VARIABLE Next_ID integer;
BEGIN
ID = 1;
SELECT MIN(ID)
FROM Table
INTO
:Next_ID;
WHILE (Next_To > ID) DO
BEGIN
SUSPEND;
ID = ID +1;
END
FOR
WITH TMP (ID) AS /*TMP.ID will contain first record of gap*/
(SELECT TF.ID+1
FROM Table TF
LEFT JOIN Table TFN ON TF.ID + 1 = TFN.ID
WHERE TFN.ID IS NULL)
SELECT T.ID, MIN(TT.ID) /*TT.ID is first record after end of gap)
FROM TMP T
JOIN Table TT ON TF.ID < TT.ID
GROUP BY 1
INTO
:ID, :Next_ID
DO
BEGIN
WHILE (Next_ID > ID) DO
BEGIN
SUSPEND;
ID = ID +1;
END
END
END
Though this is more complicated logic and I am uncertain to which extent it
benefits performance-wise.
<The rest is off topic>
>> Once again, your keen I found an error!
>
>I swear, English is my mother tongue. Although, from the above, you'd never
>know it!
>
>Once again, your keen *eye* found an error!
Ah, at first I wondered whether it meant 'your keen self found an error',
referring to my irritating skill to notice minor mistakes everywhere.
I'd say the difference between a foreigner and a native English speaker is that
some foreigners always do the same mistake, natives tend to do mistakes like
this occasionally. I typically have a problem with the difference between RISE
and RAISE, don't know whether I would have had less problems if English had
been my native language. Also, I have observed one similar, but opposite,
phenomena in Norwegian: AND and TO are pronounced identically, but spelt
differently, and many Norwegians often write the wrong one. I think immigrants
learning Norwegian are less prone to this mistake, though once they get fluent,
some of them also start having this problem!
Set
