computed column on another computed column - howTo

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

computed column on another computed column - howTo

Author

Topic

Jay123
Yak Posting Veteran

54 Posts

Posted - 2009-11-19 : 04:58:03

How do i use a computed columns results on another coputed column.

i tried useing UDF's but it still gives the error:

" Computed column 'totalSellPrice' in table 'Sold' is not allowed to be used in another computed-column definition. "

here is my udf code along with the create table code:

CREATE FUNCTION totalPrice(@quantitySold int, @productSellPrice smallMoney)
RETURNS smallmoney
AS
BEGIN
RETURN(@quantitySold * @productSellPrice)
END

CREATE FUNCTION dbo.totalAfterFees(@totalSellPrice smallMoney, @deliveryCharge smallMoney, @deliveryCost smallMoney, @ebayListingFees smallMoney, @ebayFinalSellFees smallMoney, @payPalCharge smallMoney)
RETURNS smallmoney
AS
BEGIN
RETURN(@totalSellPrice + @deliveryCharge - @deliveryCost - @ebayListingFees - @ebayFinalSellFees - @payPalCharge)
END

create table Sales.Sold
(
productName varchar(100) not null,
quantitySold int not null,
productSellPrice smallMoney not null,
totalSellPrice as dbo.totalPrice (quantitySold, productSellPrice),
DeliveryCharge smallMoney not null,
deliveryCost smallMoney not null,
ebayListingFees smallMoney not null,
ebayFinalSellFees smallMoney not null,
payPalCharge smallMoney not null,
totalAfterFees as dbo.totalAfterFees (totalSellPrice, deliveryCharge, deliveryCost, ebayListingFees, ebayFinalSellFees, payPalCharge),
CustomerID int Foreign Key(CustomerID) References Sales.Customer,

)

What am i doing wrong/ what should i be doing different.

Thanks for looking and trying to help.... Jay

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2009-11-19 : 05:24:57

Try this?


create table Sales.Sold
(
productName	 varchar(100) not null,
quantitySold	 int not null,
productSellPrice	smallMoney not null,
totalSellPrice	 as dbo.totalPrice (quantitySold, productSellPrice),
DeliveryCharge smallMoney not null,
deliveryCost	 smallMoney not null,
ebayListingFees	 smallMoney not null,
ebayFinalSellFees	smallMoney not null,
payPalCharge	 smallMoney not null,
totalAfterFees	 as dbo.totalAfterFees (dbo.totalPrice (quantitySold, productSellPrice), deliveryCharge, deliveryCost, ebayListingFees, ebayFinalSellFees, payPalCharge), 
CustomerID	 int Foreign Key(CustomerID) References Sales.Customer,

)

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

Jay123
Yak Posting Veteran

54 Posts

Posted - 2009-11-19 : 05:50:14

Cheers Charlie, That works perfect.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources