sorry but what would be the SQL to test existence of at least one row in a
possibly very large table without the need to make correlations to another
table
Alan
-----Original Message-----
From: Helen Borrie [mailto:helebor@...]
Sent: Friday, 18 October 2002 21:39
To: ib-support@yahoogroups.com
Subject: RE: [ib-support] Re: Can SLCODE 100 be trapped in an SP?


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.
>

Yahoo! Groups Sponsor
ADVERTISEMENT




To unsubscribe from this group, send an email to:
ib-support-unsubscribe@egroups.com



Your use of Yahoo! Groups is subject to the Yahoo! Terms of Service.



[Non-text portions of this message have been removed]