--- In firebird-support@yahoogroups.com, "phil_hhn" wrote:

> --- In firebird-support@yahoogroups.com, Svein Erling Tysvær wrote:
> > Put a little bit more intelligence
> > into your SQL generator and you would - in your case - end up with
> > BETWEEN 1 AND 5000 rather than the excessively long IN clause.
> Because of the issue above (a non-contiguous list of PK's), once the
> selection is known I suppose we could examine the list for sequences
> of contiguous PK's......
> Hmmmm...

Hi again Phil!

I wasn't talking about PK's at all, I just assumed people started with
one dataset and then deleted/reordered etc. at most a few times
(hopefully <10 times in most cases) until they got the records they
wanted. Though, you're right, we do need to take heed of the PK to
ascertain the correct records are deleted. Let's take an example, a
school that wants to buy books:

SELECT *
FROM BOOKS
WHERE PRICE < 100
ORDER BY Title, PK

Then the headmaster deletes from his list several books starting from
"Harry Potter" with PK 100 up to and including "Mickey Mouse and the
Firebird Conference" with PK 83 and decides to reorder based on the
available number of books. Then the SQL would change to

SELECT *
FROM BOOKS
WHERE (PRICE < 100)
AND (Title NOT BETWEEN "Harry Potter" AND "Mickey Mouse and the
Firebird Conference" OR (Title = "Harry Potter" AND PK < 100) OR
(Title = "Mickey Mouse and the Firebird Conference" AND PK > 83)
ORDER BY Availability, PK

The headmaster knows he needs at least 200 copies and deletes the
books with less than 200 available copies, the last one having PK 851.
Again, the SQL changes (note, I just add to the SQL):

SELECT *
FROM BOOKS
WHERE ((PRICE < 100)
AND (Title NOT BETWEEN "Harry Potter" AND "Mickey Mouse and the
Firebird Conference" OR (Title = "Harry Potter" AND PK < 100) OR
(Title = "Mickey Mouse and the Firebird Conference" AND PK > 83))
AND (Availability > 200 OR (Availability = 200 AND PK > 851)

Similarly, if only one record was deleted, then you would use 'NOT ='
or 'NOT IN'. Not too complex at all, but I guess a too general
solution could leave you with less than ideal PLANs.

HTH,
Set