Hi Mitch!

I think this is off-topic here. You are asking about a SQL issue.
Anyway...

You can't do

Select * from Item where
Project = :ProjectID and
(:LocationID is null or (Location = :LocationID))

because you'll get a "Datatype unknown" back from the server. So you
can instead:

Select * from Item where
Project = :ProjectID and
(:LocationID_Sel = 'F' or (Location = :LocationID))

This introduces a new parameter, though. The server will know
:LocationID_Sel is a CHAR(1). Give 'F' (or null - but avoid that) to
have all locations and 'T' (or anything else nut NULL) to have the one
specified in :LocationID. If LocationID is an integer and there is
absolutely never a specific value in that column, for example 0, you
could do

Select * from Item where
Project = :ProjectID and
(:LocationID = 0 or (Location = :LocationID))

(your question seems to imply that you don't want any additional
parameters) but I think you'll run into trouble - it looks dangerous.
I use that type of construct only when I supply the gui with a
threestate checkbox ('T', 'F' or ' ') where the last one is "dont' care".

Also remember that there is a firebird.conf setting called something
like "Full boolean evaluation" and with that option turned on this
will not work. On the other hand that option is "freaky" and should
only be used for legacy stuff.

I have used theese kind of constucts a lot with FB from 1.0 to 1.5.3
and never had the optimizer freak because of it. But you might want to
check the PLAN to make sure you won't get a worse one.

IBO has a "macro" capability and you might want to check that out too,
but changing the value for a macro will most certainly render a
re-prepare.

HTH,

/Dany

--- In IBObjects@yahoogroups.com, "mitch_landor" <mlq97@...> wrote:
>
> Im using TIBOQuery.
>
> How can I easily query an integer column to get all the rows matching
> an integer or also allow all rows to be fetched? EG:
>
> Item.Project (integer)
> Item.Location (integer)
>
> (Case 1)
> Select * from Item where
> Project = :ProjectID and Location = :LocationID
>
> (Case 2)
> Select * from Item where
> Project = :ProjectID and Location = all locations
>
> Thanks
> Mitch
>