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.

Reply via email to