Question #1:
I have a form (in a multi-user app) that uses several views as the main source of data. I have a rotation system setup to switch the main data source between these views depending on what the user asks to see. Obviously these views all return the same fields. They just query for them based on different criteria.
I created a view with a literal in the where clause to handle the difference in criteria. This is an example of the view query:
SELECT People.*, ;
Firm.company, ;
Firm.sortcomp ;
FROM firm, people ;
WHERE people.firmid = firm.firmid and ;
people.id > 0 ;
&gcExecFlex. ;
ORDER BY People.last, ;
People.first
This works just fine until I want to use another table in the join. Like this:
SELECT People.*, ;
Firm.company, ;
Firm.sortcomp ;
FROM firm, people, ok ;
WHERE people.firmid = firm.firmid and ;
people.id > 0 and ;
people.id = ok.id and ;
ok.selected = .t. ;
&gcExecFlex. ;
ORDER BY People.last, ;
People.first
Apparently you can't vary the FROM part of the query.
So... Suggestion?
Can I create views on the fly?
Question #2:
This app was originally created in VFP3 where there are no outer joins. We cheated and added records in each related table with a dummy id to allow the join to happen.
We're now in VFP5... Now that Left Outer Joins are available should we use them? I've done some testing and it appears that our cheat is faster.
Opinions?
Thanks in advance!
paul