At 06:02 PM 18/03/2004 +0000, you wrote:

>I am using TIBOQuery and I need to use a substring of on of the
>fields in my query to make the WHERE clause work. However, when I try
>to run my application (in Delphi 6) I throw an exception, SQL Error -
>804, Function unknown SUBSTR.
>
>My where clause appears as below ...
>
>WHERE
> "Transactions"."TransactionTypeID" = '7110' AND
> (substr("Transactions"."CrossReference",1,1) = 'A' OR
> substr("Transactions"."CrossReference",1,1) = 'V')

You are getting the error because the UDF substr() isn't declared in the
database. Firebird has an internal function SUBSTRING. But a function is
not as efficient as
WHERE
"Transactions"."TransactionTypeID" = '7110' AND
"Transactions"."CrossReference" STARTING WITH 'A' OR
"Transactions"."CrossReference" STARTING WITH 'V'

To use SUBSTRING:

WHERE
"Transactions"."TransactionTypeID" = '7110' AND
SUBSTRING("Transactions"."CrossReference" FROM 1 FOR 1) IN ('A' , ''V')

If you are using IB, you will need to declare and use the UDF.

>I know I can accomplish this with a where clause like this ...
>
>WHERE
> "Transactions"."TransactionTypeID" = '7110' AND
> ("Transactions"."CrossReference" LIKE 'A%' OR
> "Transactions"."CrossReference" LIKE 'V%')
>
>but later on I will need to look at substrings that are not just the
>first character in the field (allowing me to use LIKE)

Like is for wildcard searches. If you are looking for an enclosed string,
use CONTAINING:

WHERE
"Transactions"."TransactionTypeID" = '7110' AND
"Transactions"."CrossReference" CONTAINING 'XYZ' OR
"Transactions"."CrossReference" CONTAINING 'PQR'

CONTAINING is case-insensitive.

Helen

Do you have the IB manuals, esp. LangRef.pdf?

Helen