You need to add the inner join to the correlated subquery. It must be a
standalone query with all parts defined.
-----Original Message-----
From: "MB Software Solutions" <[EMAIL PROTECTED]>
To: "Profox" <profox@leafe.com>
Sent: 3/1/07 11:24
Subject: Need help with VFP9 SQL JOIN subquery
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
_______________________________________________
Post Messages to: ProFox@leafe.com
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.
