Cindy,

The thread, I mentioned, is Troubleshooting Re: SQL - SELECT: 'Too many subqueries' Thread #319286 Message #319693

>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?