Josh,

Its Oracle. It has 5 years of hospital and clinic business office data for decision support for the individual departments. My codes are for certian office procedures (CPT codes).

So how do you do it with MS SQL?


>Cindy,
>
>What kind of server are you using? Some servers will allow you to create a temporary table even if you don't have rights to create a permanent table. I'm pretty sure you can do this with SQL Server, for example. If you need more info let me know.
>
>
>>Thanks John,
>>
>>My first thought was put my list on the server, but my permissions are read-only.
>>
>>Everyone uses and ACCESS front end for this data, where it's easy to query related to the local table. But I'm doing more with the data when I get it back. I'll probably stick with pulling too many records (excuse for a long lunch) and throwing out what I don't want in a second step.
>>
>>
>>>Hi Cindy ----
>>>
>>>You are going to have problems with this no matter what you do. If the codes are small enough, you can always do a SCAN through the code table and build a character string and then use the character string within the SQL statement via macro expansion. If this won't work for you, my suggestion would be to port the local table to the database/device of the server data. The only other alternative I see is to create a local cursor through SPT encompassing ALL GiantServerDatabase records and then create another local cursor using IN (SELECT...). Not great either.
>>>
>>>
>>>>(VFP6) I want to do an SPT query like
>>>>
>>>>SELECT LotsOfData FROM GiantServerDatabase ;
>>>> WHERE GiantServerTable.Code ;
>>>> IN (SELECT cCode FROM LocalTable)
>>>>
>>>>to avoid pulling back so many records and then throwing out the ones I don't want. The LocalTable has 57 codes, and at 20+ minutes per query, I don't want to loop through my 57 codes and do 57 parameterized queries.
>>>>
>>>>This is data for a report which will be run monthly.
>>>>
>>>>Any advice? Thanks.