IN is notoriously slow - at least it is on MS SQL and Interbase. Better to
do a join if you can.
Grant Black <[EMAIL PROTECTED]> on 21/04/99 10:49:28
Please respond to [EMAIL PROTECTED]
To: Multiple recipients of list delphi <[EMAIL PROTECTED]>
cc: (bcc: Peter Jones/Logistics&Information
Technology/Christchurch/Foodstuffs)
Subject: RE: [DUG]: Query Parameters
Or would it be easier to maintain the list in a (tempory?) table and do
a subquery?
pseudo sql as follows:
select *
from table
where somefield in
(
select listfield from temptable
)
Grant Black
Software Developer
SmartMove (NZ) Ltd
Phone: +64 9 361-0219 extn 719
Fax : +64 9 361-0211
Email: [EMAIL PROTECTED]
> -----Original Message-----
> From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]
> Sent: Wednesday, April 21, 1999 9:23 AM
> To: Multiple recipients of list delphi
> Subject: RE: [DUG]: Query Parameters
>
>
>
> Parameters can only be simple types, like string, integer
> etc. You can't
> use a parameter query to do this. Instead, you will need to
> construct the
> query at runtime thus:
>
> qry.SQL.Text := ' select * from table where somefield in (';
> for i = 0 to list.count - 1 do
> if i = list.count - 1 then
> qry.SQL.Text := qry.SQL.Text + list[i] + ')'
> else
> qry.SQL.Text := qry.SQL.Text + list[i] + ',';
>
> qry.Open;
>
>
>
>
>
>
> Andrew Masters <[EMAIL PROTECTED]> on 21/04/99 10:03:53
>
> Please respond to [EMAIL PROTECTED]
>
> To: Multiple recipients of list delphi <[EMAIL PROTECTED]>
> cc: (bcc: Peter Jones/Logistics&Information
> Technology/Christchurch/Foodstuffs)
> Subject: RE: [DUG]: Query Parameters
>
>
>
>
> Sorry, I may not have explained myself clearly. I'm simply
> selecting from
> one table (bad choice of names using ID). I'll try again...
>
> SELECT * FROM TABLE
> WHERE SOMEFIELD IN (:SomeValueList)
>
> I realise IN is not the most efficient but it makes
> constructing the SQL at
> runtime very handy in this case.
>
>
> -----Original Message-----
> From: [EMAIL PROTECTED] [SMTP:[EMAIL PROTECTED]]
> Sent: Wednesday, April 21, 1999 8:45 AM
> To: Multiple recipients of list delphi
> Subject: Re: [DUG]: Query Parameters
>
>
>
> It's not possible. You will have to use multiple OR's or a
> join. Joins
> are generally quicker than IN anyway.
>
>
>
>
>
>
> Andrew Masters <[EMAIL PROTECTED]> on 20/04/99 18:56:42
>
> Please respond to [EMAIL PROTECTED]
>
> To: Multiple recipients of list delphi <[EMAIL PROTECTED]>
> cc: (bcc: Peter Jones/Logistics&Information
> Technology/Christchurch/Foodstuffs)
> Subject: [DUG]: Query Parameters
>
>
>
>
> Hi All,
>
> I have a query (simplified here) like
>
> SELECT * FROM TABLE
> WHERE ID IN (:IDList)
>
> Under WISQL something like
>
> SELECT * FROM TABLE
> WHERE ID IN (2,3)
>
> works just fine. In my code if I set the param :IDList to say
> '2,3' it does
> not work (returns nothing but no exception either). When set
> to say '2' or
> '3' it works just fine. The IDList parameter is a string
> type. Is there a
> rule of parameter substitution I'm breaking here ?
>
> Thanks
> Andrew Masters
> Clinical Solutions Ltd
> Auckland, NZ
> Ph: +64-9-476-0106
> Fax: +64-9-476-0108
> e: [EMAIL PROTECTED]
>
>
> --------------------------------------------------------------
> -------------
> New Zealand Delphi Users group - Delphi List -
> [EMAIL PROTECTED]
> Website: http://www.delphi.org.nz
>
>
>
>
>
>
> --------------------------------------------------------------
> -------------
> New Zealand Delphi Users group - Delphi List -
> [EMAIL PROTECTED]
> Website: http://www.delphi.org.nz
>
>
> (UUEncoded file named: att1.unk follows)
> (Its format is: File type unknown )
>
>
>
> --------------------------------------------------------------
> -------------
> New Zealand Delphi Users group - Delphi List -
> [EMAIL PROTECTED]
> Website: http://www.delphi.org.nz
>
---------------------------------------------------------------------------
New Zealand Delphi Users group - Delphi List - [EMAIL PROTECTED]
Website: http://www.delphi.org.nz
---------------------------------------------------------------------------
New Zealand Delphi Users group - Delphi List - [EMAIL PROTECTED]
Website: http://www.delphi.org.nz
