Reference second table in computed column

[bluespud]

Is it possible to include a related table in the computation of a column. For example, if I want to store the total of LINE.QTY on ORDER.QTY.

e.g. what is the correct way to express the following pseudo dode:

alter table ORDER
add QTY as sum(LINE.QTY) where line.order_id = order.id

All I can find on MSDN site is that I can't use a subquery.

[code green]

You want to ADD a column to a table based on the result of a query.
Surely not. This makes no sense

[bluespud]

I have a number of different applications (differing technologies) accessing the data. I want to have the ORDER.QTY available to all applications without them all having to calculate it independently.

[code green]

perfectly understandable.
But why add a new column to an existing table?

[bluespud]

ORDER.ID
ORDER.CUSTOMER

LINE.ID
LINE.ORDER_ID (fk)
LINE.QTY

I want to simplify the instances of where users want to get one row back per ORDER which includes the summed QTY.

I know I could use a view, but they need update access to ORDER & there are aternate indices.

[code green]

You have chosen not to answer my question twice now.
I am also finding very difficult to follow your problem

I know I could use a view, but they need update access to ORDER & there are aternate indices.

Maybe you should rephrase the question.
Telling us the data you have and the result you are trying to achieve

[jkmyoung]

Why get hung up on the one point?
Technically he should divide the task into 2 parts.

1. Add a column. set everything to a default value, say 0. Only do this once.

2. Update this column in the table based on another value.

1 is easy, 2 is harder.

[stuckagain]

....
2. Update this column in the table based on another value.

1 is easy, 2 is harder.

I might have missed something, but 2 doesn't seem much harder to me.

If you create a trigger on the LINE table which updates the related ORDER row totals when ever the LINE values change / added / deleted ... piece of cake!

Personally, I don't do this unless I really need to optimize for speed, then I create a new column on the header table, with a prefix I know I don't edit (ussually 'agg_' for aggregate), and a default value, then create the trigger(s) for insert, update, & delete to keep the column up to date.

There is a cost, but if you are already optimizing, the pay off is probably worth it.

Good luck,
John