Converting a view based VFP 9 app to a SQL Server 2005 backend.
A couple of the local views have other views included in joins ( getting totals etc ) ( which ,by the way, I found on moving the app originally from vfp 7 to VFp 9 I had to explicitly requery the secondary view in the requery_pre() hook of my business object )
I know in the remote view I can probably do this with a correlated subquery if I think about it a bit, but I also thought I could reproduce the secondary view in a sql view and just refer to it as dbo.
This seems to work fine, at least in getting the values initially, except I am concerned about the values being refreshed if the data underlying the sql view changes between queries of the remote view. Is there a way to ensure that the SQL View will requery whenever it is called by the remote view to join to the remote view's main table? Or does it do this automatically ( I don't seem to get that result in command window/management studio testing but I may be doing it wrong )
Suggestions for proper syntax for doing the whole thing in a remote view also most welcome, but I would also like to understand issues involved in remote views calling SQL viewsCREATE SQL VIEW "V_BPSS_COMMRUN_STMTS" ;
REMOTE CONNECTION "ITFWH" AS SELECT Commrun_stmts.cid,;
Commrun_stmts.dstmtmonend,;
Commrun_stmts.cuserid,;
Commrun_stmts.dtxndatetm,;
Commrun_stmts.mbatchesinfo,;
Commrun_stmts.ccheck_number,;
Commrun_stmts.yamount,;
Commrun_stmts.dcheck_date,;
Commrun_stmts.cfacilities_id,;
Commrun_stmts.cinvoice_number,;
Commrun_stmts.cbps_stmts_id,;
Commrun_stmts.lstmtpaid,;
Facilities.cfacilityabbrev,;
vl_cb_per_cr_stmt.ytotal_revenue,;
vl_cb_per_cr_stmt.ytotal_tax,;
vl_cb_per_cr_stmt.yrent_revenue;
FROM dbo.commrun_stmts;
INNER JOIN dbo.facilities;
ON Commrun_stmts.cfacilities_id = Facilities.cid;
INNER JOIN dbo.vl_cb_per_cr_stmt;
ON vl_cb_per_cr_stmt.ccommrun_stmts_id = Commrun_stmts.cid;
WHERE Commrun_stmts.cbps_stmts_id = ?v_bps_stmts.cid;
ORDER BY Facilities.cfacilityabbrev,Commrun_stmts.dstmtmonend
BELOW IS dbo.vl_cb_per_cr_stmt in the SQL Database
SELECT ccommrun_stmts_id,
SUM(ytotal_revenue) AS ytotal_revenue,
SUM(ytotal_tax) AS ytotal_tax,
SUM(yrent_revenue) AS yrent_revenue
FROM dbo.cart_batches
WHERE (ccommrun_stmts_id IS NOT NULL)
GROUP BY ccommrun_stmts_id
Now what if records are added or changed in cart_batches between queries of the remote view V_BPSS_COMMRUN_STMTS ?
TIA, Sergey ;-)
Charles Hankey
Though a good deal is too strange to be believed, nothing is too strange to have happened.
- Thomas Hardy
Half the harm that is done in this world is due to people who want to feel important. They don't mean to do harm-- but the harm does not interest them. Or they do not see it, or they justify it because they are absorbed in the endless struggle to think well of themselves.
-- T. S. Eliot
Democracy is two wolves and a sheep voting on what to have for lunch.
Liberty is a well-armed sheep contesting the vote.
- Ben Franklin
Pardon him, Theodotus. He is a barbarian, and thinks that the customs of his tribe and island are the laws of nature.
