>Hi Hilmar,
>
>Back to my old thread..
>
>Could you tell me how to use a MAX() in an SQL to select the Max number of a unique ID in a primary field, (which is programatically used to generate an incremental unique ID for a new record) by including records marked for deletion but not packed?
>
>My problem is that the cursor ignores deleted records when doing a select, and when the ID is generated, the incremental value of the ID could already exist in the non packed table, which then generates a unique conflict error.
>
>If I could have packed my table, I would have no such problems!
>
>Thanks very much,
>
>With best regards,
>
>Steve.
It is better to keep your sequences
outside this table. Then, you will have no such conflict. Use a separate table with sequence numbers.
However, another solution - which I do not recommend in this case - is to filter the index on
not deleted().
One problem with the approach you suggest is that two users might calculate
max(primary_key) at the same time, resulting in the same primary key value.
Another problem is that it is slow. Instead of a select ... max(...), you might also simply use a GO BOTTOM to get the last record. But I still prefer a separate table for the sequences.
Difference in opinions hath cost many millions of lives: for instance, whether flesh be bread, or bread be flesh; whether whistling be a vice or a virtue; whether it be better to kiss a post, or throw it into the fire... (from Gulliver's Travels)