Compute Column

Question

Post reply

Compute Column

Sarfaraj Ahmed

SSC-Addicted

Points: 447
More actions
June 6, 2008 at 11:35 pm

#183188

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

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

Ramkumar.K Default port Points: 1405 More actions · Answer 1

you please send the whole Query which you got tried to execute..

Ram

Ramkumar . K
Senior Developer
######################
No Surrender... No Give Up....
######################

Sarfaraj Ahmed SSC-Addicted Points: 447 More actions · Answer 2

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

Ramkumar.K Default port Points: 1405 More actions · Answer 3

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....
######################

Sarfaraj Ahmed SSC-Addicted Points: 447 More actions · Answer 4

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

Jeff Moden SSC Guru Points: 1004441 More actions · Answer 5

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

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004441 More actions · Answer 6

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

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 7

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

😎

Sarfaraj Ahmed SSC-Addicted Points: 447 More actions · Answer 8

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

Sarfaraj Ahmed SSC-Addicted Points: 447 More actions · Answer 9

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

Jeff Moden SSC Guru Points: 1004441 More actions · Answer 10

You bet...

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)