Daniel,

Thanks for the info. That is getting closer. However, this solution has problems when the lcList has comma-delimited values that are greater than single digits.

For example, the following lcList string:

lcList = ['100','101','102','103']

Returns the following result set:

iCustomerId
===========
1
2
3
10
100
101
102
103

Do you have any other tricks up your sleeve or a modification to the above?

Thanks again for your help!


>James,
>Assuming that your backend is SQL Server, the following should work:
>

>lcList = ['1','2','3','4']
>lcCmd  = 'select cname from customers where CHARINDEX(RTRIM(CAST(icustomerid as char(20))), ?lcList)>0'
>? sqlexec(lnHandle,lcCmd)
>

>It's a bit of a hack, but it works :-)
>The CHARINDEX() function is similar to VFP's AT() function and returns the starting position of the specified expression in a character string.
>
>HTH
>>I'm having difficutly using the IN operator in the parameter statement in a remote view against a SQL Server table. Here is my view:
>>
>>CREATE SQL VIEW "V_CUSTOMERS" ;
>> REMOTE CONNECT "My_Connection" AS ;
>> SELECT ;
>> Customers.icustomerid,;
>> Customers.cname,;
>> Status.cstatus ;
>> FROM Customers ;
>> LEFT JOIN Status ON Status.istatusid = Customers.istatusid ;
>> WHERE CAST(Customers.icustomerid as CHAR(20)) IN (?vp_cCustomerIdList) ;
>> ORDER BY Customers.cname
>>
>>I tried performing this same operation using SPT in the following statement:
>>
>>lcList = '1,2,3,4'
>>lcCmd = 'select cname from customers where cast(icustomerid as char(20)) in (?lcList)'
>>? sqlexec(lnHandle,lcCmd)
>>
>>I have tried changing the parameter (lcList) to the following without success:
>>
>>lcList = ['1','2','3','4']
>>
>>If I don't execute this as a parameter, then it will work:
>>
>>lcCmd = 'select cname from customers where cast(icustomerid as char(20)) in (' + lcList + ')'
>>? sqlexec(lnHandle,lcCmd)
>>
>>I really need to set this up as a parameterized view. Is there something that I am missing here or is what I am trying to accomplish just not work?
>>
>>Please help! I really would appreciate it.
>>
>>TIA,
>>
>>James