---------- Original Message -----------
From: Sándor Tamás <sandortamas@...>

> I have a table, where customers can record their daily order, like
> On 29/10/2010 I'd like to have a menu B, and on 31/10/2010 I'd like to
> have A.
> So there will be two records in the table, one for each order. But I
> have to show their orders for a week, like
> Okay, so on the week starting on 25/10/2010, you have none, none, none,
> none, B, none, A ...
> So I created an SP which gives me back these records. Then I created
> the view to have a selectable, updatable pseudo-table. The SP itself takes
> approx 8sec to run.
> I use "update orderviewbyweek set MENU3='B' where
> orderdate='2010.10.25' and customer=2345" where orderdate is the start
> of the week, which third day menu has to be changed. And that update
> takes 8sec again. Now I know why. I don't know if there is any
> solution for this, or I have to handle the whole thing on the client side.
>
> SanTa

------- End of Original Message -------

Without abandoning your idea of an updateable view, we can hack at the problem
by making your view not need a stored procedure -- or at the very least, make
it smarter.

Let's try something like this:

SET TERM ^ ;
CREATE PROCEDURE UT_GET_DAYS (
FIRST_DATE Date,
LAST_DATE Date )
RETURNS (
EACH_DAY Date )
AS
declare variable early_date date;
declare variable late_date date;
begin
early_date = case when first_date < last_date then first_date else last_date
end;
late_date = case when last_date > first_date then last_date else first_date
end;
each_day = early_date;
while (each_day <= late_date) do
begin
suspend;
each_day = each_day + 1;
end
end^
SET TERM ; ^

create view my_orders as
select orders.order_number, orders.person_name, orders.desired_menu,
ut_get_days.each_day as delivery_date
from orders right join ut_get_days ('1/1/2011', '12/31/2011')
on ut_get_days.each_day = orders.delivery_date;

What I'm hoping for here is that when you do "insert into my_orders (...)
values (...) matching (person_name, delivery_date)" or "update my_orders set
desired_menu = 'B' where order_number = 15", etc. Firebird may have an
opportunity to actually issue a smart plan on the view, restricting by
orders.order_number, person_name, etc., using an index, etc. (assuming you
index the 'orders' table properly.)

The right-join is going to be an issue no matter what; I'd look for a solution
where your app selects from a view that does a right-join, but performs updates/
inserts/deletes against another view that doesn't. But within the constraints
of what you're trying to do right now ...

Can you give this a try and see how it performs? I'm not running a version of
FB that lets me do this yet, sorry!

-Philip