June 6, 2008 at 11:35 pm
dear all
Orders table
OrderID, Price
OrderDetails table
OrderID, ItemID, Quantity, Price, TotalPrice
Here I am using Orders.Price as a compute column where I want to see summation OrderDetail.TotalPrice into Orders.Price column
I put the code below into Orders.Price computed column formula
select sum(OrderDetail.TotalPrice) from OrderDetail and getting the error below.......
'orders' table
- Error validating the formula for column
'orders' table
- Unable to modify table.
Incorrect syntax near the keyword 'select'.
Incorrect syntax near the keyword 'NULL'.
What I am doing wrong......
waiting for your reply
June 7, 2008 at 12:04 am
you please send the whole Query which you got tried to execute..
Ram
Ramkumar . K
Senior Developer
######################
No Surrender... No Give Up....
######################
June 7, 2008 at 12:24 am
ramkumar (6/7/2008)
you please send the whole Query which you got tried to execute..Ram
Thanks Ramkumar
Create Order Table query
USE [testSZ]
GO
/****** Object: Table [dbo].[orders] Script Date: 06/07/2008 07:16:14 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[orders](
[orderID] [int] NOT NULL,
[orderType] [varchar](50) COLLATE Latin1_General_CI_AS NOT NULL,
[m1] [money] NULL,
[m2] [money] NULL,
[m3] [money] NULL,
[discount] [money] NULL,
[totalprice] [money] NULL,
CONSTRAINT [PK_order] PRIMARY KEY CLUSTERED
(
[orderID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
Create OrderDetails query
USE [testSZ]
GO
/****** Object: Table [dbo].[orderDetails] Script Date: 06/07/2008 07:18:32 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[orderDetails](
[orderID] [int] NOT NULL,
[mType] [varchar](50) COLLATE Latin1_General_CI_AS NULL,
[itemid] [int] NOT NULL,
[quantity] [int] NOT NULL,
[price] [money] NOT NULL,
[totalPrice] AS ([quantity]*[price])
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
USE [testSZ]
GO
ALTER TABLE [dbo].[orderDetails] WITH CHECK ADD CONSTRAINT [FK_orderDetails_orders] FOREIGN KEY([orderID])
REFERENCES [dbo].[orders] ([orderID])
Orders Table M1 will automatically show summation of totalPrice from OrderDetails table
Query like Select Sum(OrderDetails.TotalPrice) from OrderDetails where OrderDetails.Mtype='M1' and OrderDetails.OrderID = Orders.OrderID Group BY OrderDetails.OrderID
Thanks
June 7, 2008 at 12:36 am
Hi...
Just try the below..
Select Sum(OrderDetails.TotalPrice)
from OrderDetails ,orders
where OrderDetails.Mtype='M1' and OrderDetails.OrderID = Orders.OrderID Group BY OrderDetails.OrderID
Ramkumar . K
Senior Developer
######################
No Surrender... No Give Up....
######################
June 7, 2008 at 6:41 am
ramkumar (6/7/2008)
Hi...Just try the below..
Select Sum(OrderDetails.TotalPrice)from OrderDetails ,orders
where OrderDetails.Mtype='M1' and OrderDetails.OrderID = Orders.OrderID Group BY OrderDetails.OrderID
Dear Rum
Query you sent me it doesn't work when I put it on Orders.M1 compute formula
Thanks
June 7, 2008 at 12:53 pm
You can have a computed column that automatically does this. A computed column really doesn't like to refer to other tables or even other rows. Certainly, they don't like SELECT's. You could cheat a bit by turnining your formula into a scalar user defined function.
--Jeff Moden
June 7, 2008 at 1:08 pm
Of course, you also realize how dangerous this is? If you were to change the price on an item list, you would change all orders that referenced it ... even order already closed. Would really screw up the book keepers.
--Jeff Moden
June 7, 2008 at 3:01 pm
I'd have to agree with Jeff. I did take your code, and after some adjustments (my system at home is case sensitive) I have the following working, except I have no test data or expected results to know if this is what you are really trying to accomplish.
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Orders](
[OrderID] [int] NOT NULL,
[OrderType] [varchar](50) COLLATE Latin1_General_CI_AS NOT NULL,
[M1] [money] NULL,
[M2] [money] NULL,
[M3] [money] NULL,
[Discount] [money] NULL,
[TotalPrice] [money] NULL,
CONSTRAINT [PK_Order] PRIMARY KEY CLUSTERED
(
[OrderID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[OrderDetails](
[OrderID] [int] NOT NULL,
[MType] [varchar](50) COLLATE Latin1_General_CI_AS NULL,
[ItemID] [int] NOT NULL,
[Quantity] [int] NOT NULL,
[Price] [money] NOT NULL,
[TotalPrice] AS ([Quantity]*[Price])
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
ALTER TABLE [dbo].[OrderDetails] WITH CHECK ADD CONSTRAINT [FK_OrderDetails_orders] FOREIGN KEY([OrderID])
REFERENCES [dbo].[Orders] ([OrderID])
go
Select
Sum(OrderDetails.TotalPrice)
from
OrderDetails
inner join Orders
on (OrderDetails.OrderID = Orders.OrderID)
where
OrderDetails.MType = 'M1'
Group BY
OrderDetails.OrderID
drop table dbo.OrderDetails
drop table dbo.Orders
😎
June 9, 2008 at 3:14 pm
Lynn Pettis (6/7/2008)
I'd have to agree with Jeff. I did take your code, and after some adjustments (my system at home is case sensitive) I have the following working, except I have no test data or expected results to know if this is what you are really trying to accomplish.
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Orders](
[OrderID] [int] NOT NULL,
[OrderType] [varchar](50) COLLATE Latin1_General_CI_AS NOT NULL,
[M1] [money] NULL,
[M2] [money] NULL,
[M3] [money] NULL,
[Discount] [money] NULL,
[TotalPrice] [money] NULL,
CONSTRAINT [PK_Order] PRIMARY KEY CLUSTERED
(
[OrderID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[OrderDetails](
[OrderID] [int] NOT NULL,
[MType] [varchar](50) COLLATE Latin1_General_CI_AS NULL,
[ItemID] [int] NOT NULL,
[Quantity] [int] NOT NULL,
[Price] [money] NOT NULL,
[TotalPrice] AS ([Quantity]*[Price])
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
ALTER TABLE [dbo].[OrderDetails] WITH CHECK ADD CONSTRAINT [FK_OrderDetails_orders] FOREIGN KEY([OrderID])
REFERENCES [dbo].[Orders] ([OrderID])
go
Select
Sum(OrderDetails.TotalPrice)
from
OrderDetails
inner join Orders
on (OrderDetails.OrderID = Orders.OrderID)
where
OrderDetails.MType = 'M1'
Group BY
OrderDetails.OrderID
drop table dbo.OrderDetails
drop table dbo.Orders
😎
Thank You Very Much
I am agree with you and Jeff
June 9, 2008 at 3:17 pm
Jeff Moden (6/7/2008)
Of course, you also realize how dangerous this is? If you were to change the price on an item list, you would change all orders that referenced it ... even order already closed. Would really screw up the book keepers.
Jeff
Thank you very much to give me feedback
Sarfaraj
June 10, 2008 at 4:43 am
You bet...
--Jeff Moden
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply