At 03:12 PM 7/03/2003 -0800, you wrote:

>Hi Group;
>
>Is it possible to setup the SQL statement in a
>TIB_Query component as follows.
>
>DECLARE VARIABLE aNumber INTEGER
>aNumber = :theNumber
>
>if (aNumber = 1) then
> select * from table_A where ID = :ID
>else
> if (aNumber = 2) then
> select * from table_A where NAME LIKE
> '%'||:NAME||'%'
> else
> if (aNumber = 3) then
> select * from table_A where CITY LIKE
> '%'||:CITY||'%'
>
>Is this statement valid?

No. You need to set up a completely valid SQL statement in the SQL
property of the query, and use parameter assignments to push constant
values into the parameters at run-time.

All of these conditions that you are testing should be in a handling
procedure that you call when the user's criteria are accepted into the
program. This procedure needs to resolve the constant values for your
procedure so that they are passed cleanly into the parameters.

However, with IBO you can define a "stub" statement (no WHERE clause) and
vary the WHERE clause at run-time, using SQLWhereItems in the OnPrepareSQL
event. The cost of this is a complete reprepare of your statement. Where
possible, you want to set up your SQL so that the only thing that varies
from one execution to the next is the actual constant values that are
pushed into your WHERE criteria.

>Another question, is this the correct syntax for a
>like statement. I have used this syntax in stored
>procedures and it seems to work about 50% of the time.
>Sometimes I get the results I expect other times I do
>not.

Let's put it this way. It's OK to concatenate things as long as (a) you
are sure there are no nulls involved and (b) they are correct for
case. It's a waste of time passing a null through to a concatenation
expression, since it will always resolve to null.

LIKE is (IMO) a pretty useless search mechanism. For '%xxxx' style
searches, you are better to use STARTING WITH (Firebird will convert LIKE
'%xxxx' to STARTING WITH 'xxxx', but you can save some cycles by using it
instead.) For LIKE '%xxxx%', CONTAINING is better, as it is
case-insensitive. STARTING WITH and CONTAINING will use an index if there
is one; LIKE can't use an index.

Still, there are times when you don't know whether your user wants a
STARTING WITH or a CONTAINING style search, so you will want to parse and
validate to some degree, to determine what your SQLWhereItems need to be.

Helen