At 12:53 AM 17/03/2004 +0000, you wrote:

>Hi All
>
>Often I need to test whether a record exists. For example, checking
>whether a product has been sold in the last X days may have SQL that
>looks like:
>
>SELECT KEYFIELD
>FROM SALESDATA
>WHERE (PRODUCT = :someproduct) AND
> (TRANSACTION_DATE >= :somedate)
>
>Assume there could be 1000's of matching records, but I just want to
>know if at least one exists. The SQL could also perform joins across
>tables. If cursor is in a tight loop in the code (eg checking
>10,000's of products) then I want to optimise such code for best
>response.
>
>The best way I have found to do this is a TIB_Cursor, setting MaxRows
>set to 1. I could also use 'SELECT COUNT(*)' but that would have to
>get all records... I just want to know if at least one exists. As far
>as I know you can't use an 'IF EXISTS()' in this situation.

Definitely you can and it's the 100% spot-on correct way to do an
existential check.

>Can anyone suggest a faster way to do such a query using IBO
>components? I don't know much about stored procedures. Do you think
>that could help?

No, a SP could not be faster than a dynamic existential check, although in
terms of efficiency, it would be better if you could arrange your update
logic so you were not making existence checks from the client...but, if you
must, this is all you need:
SELECT 1 from rdb$database
WHERE exists( select 1 from Salesdata
where PRODUCT = :someproduct) AND
TRANSACTION_DATE >= :somedate)
The query will return 1 if a row exists, otherwise null.

Helen