array binding with 'IN' statement

[Ivan Smahin]

On Jul 26, 2012, at 11:19 AM, Ivan Smahin wrote:

> 
> On Jul 25, 2012, at 9:32 PM, George Parkinson wrote:
> 
>> hello all,
>> i'm not sure what to make of this...
>> 
>> "SELECT * FROM t WHERE t.AgencyID in (996,998,1195)"
>> returns all records where AgencyID one of 996, 998, or 1195...no problem here.
>> 
>> now, let's say i have an array->    SqlBind(1) = "996,998,1195"
>> and i use this format -> 	       "SELECT * FROM t WHERE t.agencyid IN (:1)"
>> 
>> when i run:
>> db.SqlSelect(SQLQuery, kClientSide, kNoLocks, kForwardOnly, SQLBind)
>> only records where t.AgencyID = 996 are returned.
>> 
>> is this normal?
>> can't binding be used with "IN"?
>> 
>> thanks,
>> george
>> 
>> 
> 
> 
> One more option to play with:
> 
> http://www.valentina-db.com/dokuwiki5/doku.php?id=valentina:vcomponents:vsql:reference:expr:funcs_collections
> 
> something like
> 
> "select * from t where INCLUDE_ITEM( :1, agencyid)"
> 
> with
>> SqlBind(1) = "996,998,1195"
> 
> 


Ups, sorry - it is "TODO" yet feature.
Anyway you can write own sql-function which will take a list of values (as a single string value)
and check that current field value is in that list.

http://www.valentina-db.com/dokuwiki5/doku.php?id=valentina:vcomponents:vsql:reference:expr:funcs_lists
function for working with lists.

But it could be pretty slow solution because of each candidate record must be checked - no index searches and so on. Personally I would prefer "query-concat" way.
 

-- 
Best regards,
Ivan Smahin
Senior Software Engineer
Paradigma Software, Inc
Valentina - The Ultra-Fast Database
http://www.valentina-db.com

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.macserve.net/pipermail/valentina/attachments/20120726/483df1bf/attachment-0001.html>