On Sun, Nov 7, 2010 at 01:53, samcarleton <scarleton@...> wrote:

> I have a order system, there is an order table and an orderItem table.  I would like to make the subtotal column on the order table to a select from the orderItems table to find the subtotal.  Can I do that in a computed field?  If so, how?  Is it a wise thing to do? If not, what are the other options?

you can do that, just remember that you need to enclose the select
statement in an extra pair of quotes, like this:

alter table order
add o_subtotal computed by ((
select sum(oi.oi_price)
from orderitem oi
where oi.oi_id_order = order.o_id_order
));

the above select statement will be executed for every order record you fetch.
i think it would be faster to write one select statement using a join,
like this:

select o.o_id_order, o.o_field1, o.o_field2, sum(oi.oi_price)
from orderitem oi
left join order o on o.o_id_order = oi.oi_id_order
group by o.o_id_order, o.o_field1, o.o_field2

i'm not 100% sure about the second solution, but someone will soon
correct me if i'm wrong :)

hth,
markus