We have set up a VFP database on a remote server and want to use remote views to access data there (our main reason is that in the future, if we have to access a SQL (or whatever) server, we only have to redesign the views and not rewrite any code).
Some very weird things are happening, however. When I establish the connection and then try to create a new remote view, I am presented with the dialog that asks me to create a JOIN between the two tables I've added to the view. So far so good. But the JOIN statement doesn't go into the JOIN screen, it goes into the FILTER screen! If I delete it and try to create the join by drag-and-drop, it STILL goes into the FILTER screen. If I try to create the join manually in the JOIN screen, I get an ODBC error.
What I'm trying to do is create a left outer join between my "MainPatient" table and the "SCDirectory" table, which stores the names of the patients' attending physicians. I simmply want a list of all patients with their physician's name. When I look at the SQL code that my join statement generates, however, it looks VERY strange, and in fact generates an "unrecognized phrase/keyword" error on the "from" statement.
SELECT Mainpatient.firstname, Mainpatient.lastname,;
Scdirectory.firstname, Scdirectory.lastname;
FROM {oj mainpatient Mainpatient LEFT OUTER JOIN SCdirectory Scdirectory ;
ON Mainpatient.attendingmd = Scdirectory.dirid }
If I generate this same join as a LOCAL view, it works fine, and the code looks more like what I'd expect:
SELECT Mainpatient.fc_firstname, Mainpatient.fc_lastname,;
Scdirectory.fc_firstname, Scdirectory.fc_lastname;
FROM general!mainpatient LEFT OUTER JOIN general!SCdirectory ;
ON Mainpatient.fn_attendingmd = Scdirectory.fn_dirid
Since the SQL code generated in views is not editable, I'm stuck! I'd appreciate any guidance!!!
Thanks,
Jeffery Cotton
JVCotton@aol.com
|
