CODEWITHSUNDEEP
c#foxprovisual-foxpro
saTech
saTech

Reputation: 441

Microsoft Visual Foxpro Syntax

I'm facing a weird issue, I'm running a query in the VFP9 command window, and it's working fine. But the same query in VFP7 command is throwing me an error with syntax.

Query 

SELECT 
    a.Prov_Site, Office, a.billNum, a.invoicDate, a.TotalCharg, price 
FROM 
    invoices AS a 
LEFT JOIN 
   ( SELECT billNum, SUM(price) as Price FROM Items GROUP BY billNum) AS b 
ON a.billNum= b.billNumWHERE a.TotalCharg <> b.price

I am running the same query through C# code with VFP9 drivers installed, I am getting exception with syntax.

Can someone help me on this?

Thanks, Sach

Upvotes: 0

Views: 965

Answers (2)

Tamar E. Granor
Tamar E. Granor

Reputation: 3937

VFP 7 definitely doesn't support derived tables, so this query won't work there. From C#, are you using ODBC or OLE DB. The VFP ODBC drivers haven't been updated since VFP 6 and thus also don't support derived tables.

Upvotes: 2

Oleg
Oleg

Reputation: 823

Try this:

SELECT a.prov_site, a.office, a.billnum, a.invoicdate, a.totalcharg, b.price 
FROM invoices AS a LEFT JOIN 
   (SELECT billnum, SUM(price) as price FROM items GROUP BY billnum) AS b 
ON a.billnum = b.billnum WHERE a.totalcharg <> b.price

Upvotes: 0

Related Questions