SQL statements using a 'table' as in input

[Barley Man]

I need to interrogate an SQL database for the sales of items from a specific
list which is contained within a small table.

I can manage queries like:-

select * for table1.dayno= 125 from table 1

...... and, with additional text, I can use SQLconnect to get the extract
from a remote source

However, if I have a list of products which are a subset of all the products
that COULD have been sold, how do I get SQL to select the sales that were for
each item in that subset table, something like this:-

Select * where table1.stockno = [each entry in] subset from table1

Finally, as the subset is in FoxPro form, how can I 'pass' that 'list' of
stocknumbers via SQLConnect, etc., across to SQL to retrieve the correct
records

Ian

[Cindy Winegarden]

Hi Ian,

I'm not entirely sure what you want in your SQL criteria but it looks like
you want to get a list of products from a VFP table and query SQL Server
based on those items. What I do in this case is build by query statement and
send it to SQL Server via SQL Pass-through. To build my string I Scan my
criteria table.

CriteriaList = ""
Scan
CriteriaList = CriteriaList + ", '" + ;
Alltrim(CriteriaTable.SomeField) + "'"
EndScan

*-- Trim off the leading comma
CriteriaList = SubStr(CriteriaList, 2)

SQLString = "Select SomeStuff From Somewhere " + ;
"Where SomeField In (" + CriteriaList + ")"
SQLExec(nConn, SQLString), "SQLResult")

I've sent lists of 5000 items successfully in this way.

--
Cindy Winegarden MCSD, Microsoft Visual FoxPro MVP
cindy_wi...@msn.com www.cindywinegarden.com

"Barley Man" <Barl...@discussions.microsoft.com> wrote in message
news:DF81A3E0-7884-43E8...@microsoft.com...

[Barley Man]

Hi Cindy

I actually asked two linked questions and you answer deals with one of them
in a surprising way. The 'string' you built up in that Scan-Endscan pass
would be HUGE and I assumed that I could not pass anything of that length.
That certainly is one answer that I wasn't expecting! It'll work fine!

The second part of the question is: is it possible to use a
table/cursor/view as part of the search criteria in an SQL query running
wholy within Fox?

For example, I have a list of customer details in one table (custextract)
where one of the fields (custextract.refno) is the reference number of the
customers. Can I interrogate the original table (customers) using an
'SqlSelect' type statement where the search criterion is the existience of
the record within that custextract table, so long as that also contains a
reference number field (customer.refno)?

Put in faux-code:-

Extract the address from Customer where the records within Customer match
the reference number of all the records held within Custextract

or

Select customer.refno, customer.address from customer ;
where customer.refno=(all the)custextract.refno(held within custextract)

I need to run this sort of sql seclect statment wholy within Fox Pro, i.e.,
I am not interrogating an Sequel Server database in this case.

Ian

[AA]

Hi Barley Man
If Customers and Custextract are VFP DBF tables, yes you can join them with
VFP SQL queries.
SELECT whatever FROM Customers AS C JOIN Custextract AS X ON
X.somecolumn = C.somecolumn INTO CURSOR xxx
If one of them is a table in a remote database, where "remote" means NOT
VFP, such as MS SQL Server, then there are other ways:
SQLEXEC(connhandle, "SELECT * FROM Customer", 'mycursor')
SELECT * FROM mycursor AS C JOIN Custextract AS X ON X.column =
C.column INTO CURSOR xxx

or you can create a temporary copy of the DBF in the SQL Server database
and then have the remote database do the work:

lnOk SQLEXEC(connhandle, "CREATE TABLE #Temp (column1 Char(10), column1 Int,
refno Int) ")
IF lnOK>0
SELECT Custextract
SCAN
SCATTER MEMVAR
SQLEXEC(connhandle, "INSERT INTO #Temp VALUES (?column1, , ?column2,
?refno )
ENDSCAN
ENDIF
SQLEXEC(connhandle, [SELECT * FROM Customers C JOIN #Temp AS X ON
X.refno=C.refno], 'xxx' )

-Anders

"Barley Man" <Barl...@discussions.microsoft.com> skrev i meddelandet
news:D6E69F42-4FE3-4283...@microsoft.com...