SET,

> >> In a table I have records with id's 2,4,5,8. How can I receive a list
> >> with values 1,3,6,7. I have tried in this way
> >>
> >> SELECT t1.id + 1
> >> FROM table t1
> >> WHERE NOT EXISTS (
> >> SELECT *
> >> FROM table t2
> >> WHERE t2.id = t1.id + 1
> >> )
> >>
> >> but it's not working correctly. It doesn't bring all available positions.
> >
> >The SQL looks OK and should work.
> >
> >What is the datatype of the ID column of the table?
>
> No Sean, that would return 3 and 6, 1 and 7 wouldn't be returned by such a
> statement (1 because that is less than the lowest value in table and 7
> because 6 isn't in the table)

Once again, your keen I found an error!

> The best way to do something like this, is (in my
> opinion) to use EXECUTE BLOCK (it is possible to do similar things using a
> recursive CTE, but that's more complex, slower and quickly fails due to too
> many recursions):
>
> execute block returns (id integer) as
> declare variable idmax integer;
> begin
> id = 1;
> select max(id) from table into :idmax;
> while (id < idmax) do
> begin
> if (not exists(select * from table where id = :id)) then suspend;
> id = id + 1;
> end
> end

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

The above has the benefit of not requiring table reads to determine missing items.


Sean