469,072 Members | 1,860 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,072 developers. It's quick & easy.

Reference second table in computed column

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.
Sep 18 '08 #1
7 2616
code green
1,726 Expert 1GB
You want to ADD a column to a table based on the result of a query.
Surely not. This makes no sense
Sep 18 '08 #2
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.
Sep 18 '08 #3
code green
1,726 Expert 1GB
perfectly understandable.
But why add a new column to an existing table?
Sep 18 '08 #4
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.
Sep 18 '08 #5
code green
1,726 Expert 1GB
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
Sep 19 '08 #6
jkmyoung
2,057 Expert 2GB
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.
Sep 19 '08 #7
....
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
Sep 19 '08 #8

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

9 posts views Thread by DMAC | last post: by
7 posts views Thread by Wolfgang Kreuzer | last post: by
reply views Thread by Jim Heavey | last post: by
7 posts views Thread by slitvinov | last post: by
7 posts views Thread by Aamir Mahmood | last post: by
reply views Thread by zhoujie | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.