I have a view, that needs to be filtered at run time. What I want to do is use SET FILTER TO, but the rest is a select statement. sample:
SET FILTER TO bed_key NOT IN (SELECT bed_key FROM bookingtbl WHERE ;
BETWEEN(m.arrive_date, bookingtbl.arrive_date, bookingtbl.dept_date);
UNION(SELECT bed_key FROM occupantstbl WHERE ;
BETWEEN(m.arrive_date, occupantstbl.arrive_date, occupantstbl.dept_date)))
The actual SELECT's works fine. It is only when I add the SET FILTER part that I get the syntax error. I have tried doing it to a cursor,(correct results) and SET FILTER TO bed_key NOT IN tempcur, syntax error.
Is this the result of a limitation of SET FILTER? BTW, I tried to do it at the actual view, but only the first half of the SELECT would fit.