When I createed a local view for access to 2 Visual FoxPro tables (VFP 6.0), I specifically wanted the main key condition to hit first; however, after I saved the view, FoxPro changed the order of the where clause conditions, which causes the prgoram to run longer than it would with a SQL statement with the WHERE clause conditions in the right order, as follows:
SELECT Pocosts.po_main, Pocosts.project_no, Pocosts.cs_desc,;
Pocosts.cs_date, Pocosts.cs_amount, Apsumfil.paid_date,;
Apsumfil.invoice_no, Apsumfil.vendor_no;
FROM po!pocosts, apsumfil;
WHERE Pocosts.vendor_no = Apsumfil.vendor_no;
AND Pocosts.invoice_no = Apsumfil.invoice_no;
AND Pocosts.po_main = ?ThisForm.cPO_Main;
ORDER BY Apsumfil.invoice_no
I wanted the condition, Pocosts.po_main = ?ThisForm.cPO_Main, to hit first in the WHERE clause.
SELECT Pocosts.po_main, Pocosts.project_no, Pocosts.cs_desc,;
Pocosts.cs_date, Pocosts.cs_amount, Apsumfil.paid_date,;
Apsumfil.invoice_no, Apsumfil.vendor_no;
FROM po!pocosts, apsumfil;
WHERE ;
Pocosts.po_main = ?ThisForm.cPO_Main; AND ;
Pocosts.vendor_no = Apsumfil.vendor_no AND;
Pocosts.invoice_no = Apsumfil.invoice_no;
ORDER BY Apsumfil.invoice_no
When I run the above raw SQL code, outside of a view, it runs faster, but if I leave it the way VFP writes it int he local view, it runs very slowly.
How do I make VFP leave it the way I pit it in there? Makes me want to go back to writing SQL code instead of using Views!
Cecil