I asked this question a few months ago and managed to figure a way around the issue without an answer.
I want to create a paramterized view that ignores deleted records. Basically, I have tried everything I know of and can't figure out how. Here one incarnation of the SQL from the view designer:
SELECT somestuff
FROM temptest!convention INNER JOIN temptest!exhibconv;
INNER JOIN temptest!exhibitor ;
ON Exhibconv.ecexhibitnum = Exhibitor.exhibitnum ;
ON Convention.convnum = Exhibconv.ecconvnum;
WHERE Convention.convnum = ?m.convnum;
AND DELETED('exhibconv') = .F.;
ORDER BY Exhibitor.company
What happens is that the query returns different results depending on where the record pointer sits in the table. (Deleted() isn't re-evaluated for every row, just once at the beginning of the query.)
So I tried:
SELECT somestuff, deleted('exhibconv') as deadrecord
FROM temptest!convention INNER JOIN temptest!exhibconv;
INNER JOIN temptest!exhibitor ;
ON Exhibconv.ecexhibitnum = Exhibitor.exhibitnum ;
ON Convention.convnum = Exhibconv.ecconvnum;
WHERE Convention.convnum = ?m.convnum;
AND deadrecord = .F.;
ORDER BY Exhibitor.company
Same results. SQL/VFP HAS to have provided for this, how do I do it?
Erik Moore
Clientelligence