Thank you for all the tips. I have the IBO help files, which are not the most useful, but I do have the IB LangRef.pdf manuals which are slightly better. Still, I have found the actual help section on the IBObjects web site the best for general explanations and 'how to' examples. I will be doing a lot of work with IBObjects over the next few months.

I actually have solved this problem before I left last night. Of course now that I have the query working, I will need to integrate this into a QuickReport today.

Yours tips on performance and efficiency are most useful.

Thanks,

Eric

Eric Tishler
Software Architect
Resolute Partners, LLC
Phone: 203.271.1122
Fax: 203.271.1460
etishler@...

-----Original Message-----
From: Helen Borrie [mailto:helebor@...]
Sent: Thursday, March 18, 2004 6:02 PM
To: IBObjects@yahoogroups.com
Subject: Re: [IBO] Trouble creating a query using SUBSTR

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



___________________________________________________________________________
IB Objects - direct, complete, custom connectivity to Firebird or InterBase
without the need for BDE, ODBC or any other layer.
___________________________________________________________________________
http://www.ibobjects.com - your IBO community resource for Tech Info papers,
keyword-searchable FAQ, community code contributions and more !



_____

Yahoo! Groups Links
* To visit your group on the web, go to:
http://groups.yahoo.com/group/IBObjects/

* To unsubscribe from this group, send an email to:
IBObjects-unsubscribe@yahoogroups.com <mailto:IBObjects-unsubscribe@yahoogroups.com?subject=Unsubscribe>

* Your use of Yahoo! Groups is subject to the Yahoo! Terms of <http://docs.yahoo.com/info/terms/> Service.


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