Nadya,

I send SELECT statements all the time to our Oracle Rdb database with 100 items in the IN(...) clause. Sometimes numeric, sometimes character values. Be sure that your numeric values do not have quotes and that your character values do.

I've just tried it in the command window and the IN (...) syntax works fine there too.

Ultimately there must have been something else going on with your query, and I assume it's much more complex than the simple one I just tested. One thing you can do to debug when you have put the SQL together as a character string is to use the debugger to stop after you have put the SQL statement together and STRTOFILE() the statement to see what you have actually put together.



>Cindy,
>
>Does it work properly? This was the fisrt thing I tried to implement too, but I found, that it doesn't work. Even in command window, I believe, it doesn't return the correct result. Therefore I ended by using bunch of INLIST(). I'll try to find this thread and I also will do more tests, to confirm, can we use this function or not. In my application another problem was that I needed it to work with NOT too (if I check Exclude option).
>
>>Rox,
>>
>>I've had situations where I needed to extract data from an SQL database for a list of about 100 patients. I have read access only to this data, so I could not upload my 100 PatId's and SELECT ... WHERE IN (SELECT * FROM MyHundred).
>>
>>This is what I've done:
>>

>>cMyWhereStatement = ;
>>    [WHERE Invoices.Idx_id IN (]
>>
>>cMyIds = []
>>SELECT Patient_list
>>SCAN
>>    cMyIds = cMyIds + [,"] + ALLT(STR(PatId)) + ["]  && Remove "'s for numeric
>>ENDSCAN
>>cMyIds = SUBSTR(cMyIds, 2) + [)]    && Remove extra comma on front
>>

>>
>>Then I add all the parts of the SELECT and send the statement to the SQL server. If I'm doing local data I use macro expansion: &cMySQLStatement.
>>

---

>>>OK since we're on the subject here...
>>>Whenever I have a situation like Randall's or Nadya's where I want to check for existence/non-existence of a value in a list, I always try to work it out with a subquery like Cindy has shown here with either WHERE EXISTS or WHERE NOT EXISTS as appropriate. The reason being is both the SQL IN clause and VFP's INLIST function basically boil down to hardcoding.
>>>
>>>Back in VFP 5 I tried to get both INLIST() and IN() to work with variable values and never did get to work out. Anybody else know of a way to use either of these without hardcoding the list of values they use?