>>>>site = 'HSC'
>>>>This works:
>>>>?inlist(site,'xxx','HSC') && returns .T.
>>>>
>>>>This does NOT work:
>>>>clist = "'xxx','HSC'"
>>>>?inlist(site,clist) && returns .F.
>>>>
>>>>I need to have a dynamic list (e.g. clist = ...) so that
>>>>I can use this code to conditionally delete records. Not
>>>>sure why this will not work.
>>>>
>>>>Any ideas or alternative solutions?
>>>Why doesn't work and how would work has already been told at its best.
>>>Alternative solution might be SQL delete. As I read your "conditionally delete" I sensed you first do an SQL to collect the list :)
>>
>>The following will work:
>>
>>clist = "'xxx','HSC'"
>>? inlist(site,
&clist)
>>
>>or
>>
>>cListOfTestVals = "'xxx','HSC'"
>>cCmdExpn = "inlist(site," + cListOfTestVals + ')'
>>? &cCmdExpn
>>
>>or even better, unless it's being evaluated multiple times, in which case macro expansion once is cheaper than many EVAL()s. The macro expansion is cheaper in a FOR or WHERE clause that's executed against a set of records, where EVAL() would have to fire for each record, but macro expansion would only fire once, with the resultant expression code executed against each record instead.
>>
>>cListOfTestVals = "'xxx','HSC'"
>>? EVAL("inlist(site," + cListOfTestVals + ')')
>>? &cCmdExpn
>>
>>
>>>Cetin
>
>
Agree :) if clist is at hand already. If he has to do another SQL or scan..endscan to collect the list, I vote for doing it in one SQL delete.
>What I "sensed" from the question that he was doing :
>
select SomeField from Sometable into array myArray
>* Build clist
>delete for inlist(site, &clist)
>
>* And I would do :
>delete from myTable ;
> where ;
> site in ;
> (select distinct SomeField from sometable where ...)
Cetin
You're quite right, and if the goal is deletion, you need not use a nested select for the IN clause:
cList = "'xxx','HSC'"
DELETE FROM MyTable WHERE site in (&cList)
works, too! So if it's built by someone manually entering/picking a list of territories in a form (maybe someone like me has a mover box that contains customer types and users move the ones they want into the right hand listbox and a string gets built from that), the SQL Delete can take advantage of the string as well as a table.