Using 'IN' in SELECT statement

[Chris]

I'm trying to create a parameterized SQL Select statement to pass to an IB
6.0 opensource DB via a ClientDataset. In trying to test the SQL statement
the code generates I've run into difficulty with two items: using "IN" in a
select statement and trying to run a parameterized query in IBSQL or IB
Workbench.

Here's the SQL statement the code generates:

SELECT 1, p.* FROM Prox_Data p
WHERE (STRIPPEDPARTNUM = :pPref)
UNION
SELECT 10, p.* FROM Prox_Data p
WHERE (PARTNUMBER <> :pPref) AND (VOLTAGE in (:pVolt, "AC/DC")) AND (SHAPE =
:pShape) AND (HOUSING = :pHousing)
UNION
SELECT 20, p.* FROM Prox_Data p
WHERE (PARTNUMBER <> :pPref) AND (VOLTAGE in (:pVolt, "AC/DC")) AND (SHAPE =
:pShape) AND (not(HOUSING = :pHousing))
ORDER BY 1, LISTPRICE, PARTNUMBER

First order of business: get it to work in ISQL. The query engine complains
on the 5th line because of the "(VOLTAGE in (:pVolt, "AC/DC"))" statement.
Is it possible to use IN this way? I can't find a detailed explanation of
it in any of the IB docs or in a Google Group search. I'm a relative newbie
with IB so any help is appreciated if I'm out in left field on my syntax.

Secondly, is it possible to run a parameterized query in ISQL? IB Workbench
actually displays the parameters so I suspect there's a way to make it work.
Again, the docs are a bit thin. Is there an SQL statement that I can add to
the end of the statement above that will supply the params via a Select or
by hard-coding them into a parameter list at the end of the SQL statement?
I know I can simply replace all the params in the Union query but I have to
test this against dozens of different sets of params and am trying to find
an elegant way to do it.

Any help is appreciated. In general, is there a good source for info on
running SQL statements in ISQL? The IB 6.0 docs are very weak in that
regard.

Cheers,

Chris

[Marcos Pegoraro]

you can´t use IN in a parameterized query. Use Format function instead.
Format('SELECT 1, p.* FROM Prox_Data p

WHERE (STRIPPEDPARTNUM = :pPref)
UNION
SELECT 10, p.* FROM Prox_Data p

WHERE (PARTNUMBER <> :pPref) AND (VOLTAGE in (%s, "AC/DC")) AND (SHAPE =

:pShape) AND (HOUSING = :pHousing)
UNION
SELECT 20, p.* FROM Prox_Data p

WHERE (PARTNUMBER <> :pPref) AND (VOLTAGE in (%0:s, "AC/DC")) AND (SHAPE =

:pShape) AND (not(HOUSING = :pHousing))

ORDER BY 1, LISTPRICE, PARTNUMBER',[ValuetoFillParameter])

"Chris" <_remove_Te...@surewest.net> escreveu na mensagem
news:3eb6bfe4$1...@newsgroups.borland.com...

[@abvisie.nl Arno Brinkman]

Hi,

> SELECT 1, p.* FROM Prox_Data p
> WHERE (STRIPPEDPARTNUM = :pPref)
> UNION
> SELECT 10, p.* FROM Prox_Data p
> WHERE (PARTNUMBER <> :pPref) AND (VOLTAGE in (:pVolt, "AC/DC")) AND (SHAPE
=
> :pShape) AND (HOUSING = :pHousing)
> UNION
> SELECT 20, p.* FROM Prox_Data p
> WHERE (PARTNUMBER <> :pPref) AND (VOLTAGE in (:pVolt, "AC/DC")) AND (SHAPE
=
> :pShape) AND (not(HOUSING = :pHousing))
> ORDER BY 1, LISTPRICE, PARTNUMBER

> First order of business: get it to work in ISQL. The query engine
complains
> on the 5th line because of the "(VOLTAGE in (:pVolt, "AC/DC"))" statement.
> Is it possible to use IN this way?

Yes, but use single quotes. (:pVolt, "AC/DC") => (:pVolt, 'AC/DC')

Regards,
Arno Brinkman
ABVisie

-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
Firebird links :
http://www.firebirdsql.com
http://www.firebirdsql.info
http://www.fingerbird.de/
http://www.comunidade-firebird.org/

Nederlandse firebird nieuwsgroep :
news://80.126.130.81

[Chris]

> > First order of business: get it to work in ISQL. The query engine
> complains
> > on the 5th line because of the "(VOLTAGE in (:pVolt, "AC/DC"))"
statement.
> > Is it possible to use IN this way?
>
> Yes, but use single quotes. (:pVolt, "AC/DC") => (:pVolt, 'AC/DC')
>
> Regards,
> Arno Brinkman

Yep, you're right Arno. I figured this out after I posted. The double
quotes were the problem. Now, if I can just get IB Workbench to actually
keep the parameters I enter in its history...I'll ask Martijn Tonies about
that!

Cheers,

Chris