CUIN Kaczy wrote:

>
> Hi,
>
> this is related to the previous thread where Michael L. Horne was
> looking for a single existing record.
>
> I have a table where records are linked to the record in other table
> (standard one-to-many relation).
>
> I need to find out during delete, if there are more then x other
> records remaining, and if no, than delete the parent record with all
> remaining siblings.
>
> for now I am doing it like this:
> check if count(1) is more then 3 if true, delete the record, if no
> delete the parent and let the cascase delete do its stuff.
>
> is there any better way? something like "count the records until you
> reach the minimal value"?

Well, there are alternative ways, it will vary from situation to
situation whether they are any better (don't bother if the maximum
count is 5). One would be to do something like:

...
from MyTable M1
...
where M1.Value = 'Something' and
exists(select * from MyTable M2
where M2.Value || '' = M1.Value and M2.PK < M.PK
and exists(select * from MyTable M3
where M3.Value || '' = M2.Value and M3.PK < M2.PK))

(I added the || '' simply because it is never useful to use further
indexes when you're already using the primary key)

This could of course be expanded if the value is more than three, but
the drawback is that the number of nested exists would expand as the
number of records to check for increases.

Another option would be to read a value from the records and then
check for end-of-file or similar when reaching the treshold. This
could also be incorporated in a stored procedure and would be
flexible. Unfortunately, I don't think EXECUTE IMMEDIATE is capable of
doing a FOR SELECT with further processing (that would have enabled
you to write a stored procedure that took the number of records to
check for as well as the table name as input parameters).

Set