Dear all

I have currently a table tSaleInvoice in my database. This tables contains records belonging to two types of invoices, Manufacturing Sale Invoice and Trading Sale Invoice. I have a field iRecType to distinguish the between the 2 kinds of invoices.

As an apart but for reference, I have a parameterized Remote View (RV) in Visual FoxPro (VFP) frontend (see VFP code below) that will either bring in records of either types of invoice over the wire for the users.

Now my questions are, from a performance point of view...
1) Is it fine the way it currently is?
2) Is it advisable to have 2 views of my original table tSaleInvoice in FireBird with hardcoded iRecType = 0 and iRecType = 1 and base my VFP-RVs on these 2 FB-Views
3) Is it advisable to have 2 tables tSaleInoiceManu and tSaleInvoiceTrad and base individual VFP-RVs onto these 2 tables.

Please advise.

Thanks and regards
Bhavbhuti




SELECT tSaleInvoice.*, ;
Buyer.cName AS cBuyerName, ;
Buyer.cCode AS cBuyerCode, ;
FROM tSaleInvoice tSaleInvoice ;
LEFT JOIN mAccounts Buyer ;
ON Buyer.iID = tSaleInvoice.iBuyerID ;
WHERE COALESCE(tSaleInvoice.iRecType, 0) = ?vp_iRecType ;
AND tSaleInvoice.tDT BETWEEN (SELECT US1.dValue ;
FROM uSettings US1 ;
JOIN uSettings US2 ;
ON US2.iID = US1.iPID ;
WHERE US2.CKEY = 'Current Year' ;
AND US1.CKEY = 'From') ;
AND (SELECT US3.dValue ;
FROM uSettings US3 ;
JOIN uSettings US4 ;
ON US4.iID = US3.iPID ;
WHERE US4.CKEY = 'Current Year' ;
AND US3.CKEY = 'To') ;
ORDER BY tSaleInvoice.TDT, tSaleInvoice.CBK, tSaleInvoice.INO