At 07:05 PM 18-10-02 +1000, you wrote:

>ALexander, can you enlighten me?
>select exists?
>Alan

In a stored proc or trigger:

IF (EXISTS (SELECT field1 FROM ATABLE)) THEN...

In SQL:

select t1.id, <rest of output list>
from table1 t1
where <various criteria>
AND (EXISTS (SELECT field1 FROM ATABLE t2
WHERE t2.id = t1.id))
(will return the output from table1 only for rows in table1 that have a
correlated row in aTable)

The EXISTS() test is very quick because it returns true as soon as it finds
a correlating row. EXISTS SINGULAR - which predicates not just a match but
also that there is one and only one matching row - is slower because it has
to evaluate the whole table. Both are faster than SELECT COUNT(*) which
literally walks the whole table adding 1 to count for each matching row it
finds.

heLen

> -----Original Message-----
> From: Alexander V.Nevsky [mailto:ded@...]
> Sent: Friday, 18 October 2002 18:55
> To: ib-support@yahoogroups.com
> Subject: [ib-support] Re: Can SLCODE 100 be trapped in an SP?

Bill, I recommend "exists" instead "count", it is significantly
faster if you are interested just in existance at least one record.
Notice "singular" too if you are interested in existance only one
record.

> --- In ib-support@y..., Bill Katelis <bill@s...> wrote:
> > Alexander,
> > Yes - I can work around it using a 'select count(*)' or checking
> that
> > the host variable is not null.
>