array binding with 'IN' statement

[George Parkinson]

ivan,
thanks for the info!
any word on when the "select * from t where INCLUDE_ITEM( :1, agencyid)" feature will be available? 
george



________________________________
 From: Ivan Smahin <ivan_smahin at paradigmasoft.com>
To: Valentina Developers <valentina at lists.macserve.net> 
Sent: Thursday, July 26, 2012 5:51:17 AM
Subject: Re: array binding with 'IN' statement
 



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

_______________________________________________
Valentina mailing list
Valentina at lists.macserve.net
http://lists.macserve.net/mailman/listinfo/valentina
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.macserve.net/pipermail/valentina/attachments/20120727/4e3b3166/attachment.html>