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 RaiPosted Apr 13, 2014, 12:31 PM
Florian GroothuisPosted Apr 13, 2014, 5:25 AM
Rajneesh RaiPosted Apr 13, 2014, 12:35 AM
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(18,2)
)
-- Create UFD to return sum of particular OrderNumber
CREATE FUNCTION ADDSUM(@ORDERNUMBER INT)
RETURNS DECIMAL(18,2)
AS
BEGIN
DECLARE @AMOUNT DECIMAL(18,2)
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 1,1,18
UNION
SELECT 1,1,19
INSERT INTO ORDERS
SELECT 1
-- Select from tables
SELECT * FROM ORDERS
SELECT * FROM ORDERLINES
Boom !! i think it solved your problem. :)