Compute ordertotal from orderlines

Question

I'm new at this so sorry for the simple question.

I have 2 tables: orders and orderlines

orders.ordernumber

orders.ordertotal

orderlines.ordernumber

orderlines.orderline

orderlines.amount

I want to use the Computed Column Specification Formula to define the ordertotal from the orderlines. I tried something like:

SELECT SUM(amoumt) FROM Orderregels WHERE ordernumber=ordernumber

but that one returns an error.

How can I achieve what I want (if possible)?

Thnx in advance

Florian

Rajneesh Rai · Answer

Please mark my answer as accepted. Thnx

Florian Groothuis · Answer

Thx Rajneesh! That worked like a charm!

Rajneesh Rai · Answer

Hello Florian In order to use another table's column in Computed Column Specification we have to Create and use a UFD to solve this problem. -- Create OrderLine Table CREATE TABLE ORDERLINES ( ORDERNUMBER INT ORDERLINE INT AMOUNT DECIMAL(182) ) -- Create UFD to return sum of particular OrderNumber CREATE FUNCTION ADDSUM(@ORDERNUMBER INT) RETURNS DECIMAL(182) AS BEGIN DECLARE @AMOUNT DECIMAL(182) SELECT @AMOUNT = SUM(AMOUNT) FROM ORDERLINES WHERE ORDERNUMBER = @ORDERNUMBER RETURN @AMOUNT END -- Create Order Table CREATE TABLE ORDERS ( ORDERNUMBER INT ORDERTOTAL AS ( DBO.ADDSUM(ORDERNUMBER) ) ) -- Insert Dummy Data into Tables INSERT INTO ORDERLINES SELECT 1118 UNION SELECT 1119 INSERT INTO ORDERS SELECT 1 -- Select from tables SELECT * FROM ORDERS SELECT * FROM ORDERLINES Boom !! i think it solved your problem. :)