I know the new functionality in VFP9 SQL is cool like Oracle had where
you can defined joins to subqueries, but I seem to have a flaw in my
code. (see below).
select v1.iid, ;
v1.nMatlID, ;
v1.nVendorID, ;
d1.nUnitCost, d1.dActive, ;
v1.cVendorSKU, ;
lu1.cCompany;
FROM eiwdb!luMaterialVendors v1 ;
JOIN eiwdb!luCompanies lu1 ;
ON lu1.iid = v1.nMatlID ;
join (select iid, nunitcost, max(dActive) from eiwdb!luMaterialVendors
v2 where v2.nMatlID = v1.nMatlID) d1 on v1.iid = d1.iid ;
WHERE v1.nMatlID = ?viMatlID ;
ORDER BY lu1.cCompany
I'm trying to get the latest price record. The luMaterialVendors table
holds the pricing history for a given material to a vendor. When I try
this in ViewEditor 3.0.126, it tells me that alias v1 is not found.
I've defined "v1" immediately in the FROM clause. Ideas?
tia!
--Michael
--
Michael J. Babcock, MCP
MB Software Solutions, LLC
http://mbsoftwaresolutions.com
http://fabmate.com
"Work smarter, not harder, with MBSS custom software solutions!"
_______________________________________________
Post Messages to: [email protected]
Subscription Maintenance: http://leafe.com/mailman/listinfo/profox
OT-free version of this list: http://leafe.com/mailman/listinfo/profoxtech
** All postings, unless explicitly stated otherwise, are the opinions of the
author, and do not constitute legal or medical advice. This statement is added
to the messages for those lawyers who are too stupid to see the obvious.
