Select SQL using table for WHERE

[MikeMV]

Hello,

I am running VFP9 SP2 to extract data from an MS SQL 2000 DB. I would like to use a field on a VFP stand alone table as a list for the WHERE clause, but I am getting an error. I would like to know if you can see what I am doing wrong from the code I am using below:

CREATE SQL VIEW vOrderMaster;
REMOTE CONNECTION CustServ;
AS SELECT [tbl Order Status Tracking Master].SalesOrderNumber,[tbl Order Status Tracking Master].Value,;
[tbl Order Status Tracking Detail].ID,[tbl Order Status Tracking Detail].StatusNumber,;
[tbl Order Status Tracking Detail].Date,[tbl Order Status Tracking Detail].ByWhom;
FROM [tbl Order Status Tracking Master];
INNER JOIN [tbl Order Status Tracking Detail];
ON [tbl Order Status Tracking Detail].SalesOrderNumber=[tbl Order Status Tracking Master].SalesOrderNumber;
WHERE [tbl Order Status Tracking Detail].SalesOrderNumber IN(SELECT salesorder FROM openorders);
ORDER BY [tbl Order Status Tracking Master].SalesOrderNumber

My original statement works fine, I had it setup as:

WHERE YEAR([tbl Order Status Tracking Master].OrderDate)>2007

The table names where I need to pull this information from have names with spaces, I don't like this, but I can't change that.

I will greatly appreciate any feedback you can provide.

 

[cbasoz]

You cannot use local table/cursors directly on SQL server queries. However you can create a view that uses SQL server temp table. Just before using the view or requerying you simply create or update temp table. Here is the code to generate such a view:

http://www.foxite.com/archives/remote-view-parameters-for-in-0000250797.htm

PS: On SQL server side it is better to write it as an exists query or inner join.

Cetin Basoz
MS Foxpro MVP, MCP