CODEWITHSUNDEEP
sql-server
TheTechnicalPaladin
TheTechnicalPaladin

Reputation: 89

SQL Server: Computed Column Result Used In Another Computed Column

I know its not possible to have a computed column take into consideration its calculations another computed column. I found out the hard ware with the following error:

"is not allowed to be used in another computed-column definition."

So i have the following data columns, which arent neccessarily important, but just so you understand what I am doing (any other columns referenced are standard non computed columns):

HardwareAssetDepreciableValue AS CONVERT(DECIMAL(7,2),HardwareAssetPurchaseValue - 
HardwareAssetSalvageValue)

HardwareAssetLifeSpan AS CONVERT(DECIMAL(6,2),DATEDIFF(day,HardwareAssetDateInstalled,
HardwareAssetEndOfLifeDate)) / 365

They are all calculated and work as expected, however what I am having issues with is a rather complicated set of calculations at best, but wondering if anyone can suggest or help with alternatives to resolving the issue of multiple computed columns.

My query is:

HardwareAssetAccumulatedDepreciationValue AS CASE WHEN HardwareAssetDepreciationMethodID
 = '1' THEN CONVERT(DECIMAL(7,2),((HardwareAssetDepreciableValue / HardwareAssetLifeSpan)
/ 365)) WHEN HardwareAssetDepreciationMethodID = '2' THEN CONVERT(DECIMAL(7,2),
HardwareAssetAccumulatedDepreciationValue + ((1.5 *(1/HardwareAssetLifeSpan))*
HardwareAssetBookValue)/365) ELSE CONVERT(DECIMAL(7,2),
HardwareAssetAccumulatedDepreciationValue + ((2 *(1/HardwareAssetLifeSpan))
*HardwareAssetBookValue)/365) END

Any help or advice is appreciated!

Upvotes: 2

Views: 1160

Answers (3)

granadaCoder
granadaCoder

Reputation: 27852

A computed column is good for a "one off" IMHO.

For something a tad bit complex, I like to create a scalar user defined function, and wrap the "mini logic" in it.

Here is a simple Northwind example. It doesn't make alot of pratical sense, but demonstrates.

Now, I don't like the below udfExampleUdfTwoDoubleUdfOne idea. But it could work for you.

Its an idea for the arsenal of tools. Which is because you asked for alternate ideas.

Use Northwind
GO

create function dbo.udfExampleUdfOne(@OrderID as int, @ProductID int, @UnitPrice money )
        returns int
as
begin

declare @returnValue int = 0

select @returnValue = ISNULL(@OrderID,0) + ISNULL(@ProductID,0) + ISNULL(@UnitPrice,0)


return @returnValue;

end;

GO

create function dbo.udfExampleUdfTwoDoubleUdfOne(@udfOneResult int)
        returns int
as
begin

declare @returnValue int = 0

select @returnValue = ISNULL(@udfOneResult,0) * 2


return @returnValue;

end;


GO

SELECT TOP 1000 [OrderID]
      ,[ProductID]
      ,[UnitPrice]
      ,[Quantity]
      ,[Discount]
      , MyValueOne = dbo.udfExampleUdfOne(OrderID , ProductID , UnitPrice)
      , MyValueTwoWhichIsActuallyDoubleValueOne = dbo.udfExampleUdfTwoDoubleUdfOne(dbo.udfExampleUdfOne(OrderID , ProductID , UnitPrice))
  FROM [Northwind].[dbo].[Order Details]

APPEND

So trying to mimic your example

create function dbo.udfComputeHardwareAssetDepreciableValue(@HardwareAssetPurchaseValue int, @HardwareAssetSalvageValue int )
        returns int
as
begin

declare @returnValue int = 0

select @returnValue = ISNULL(@HardwareAssetPurchaseValue,0) - ISNULL(@HardwareAssetSalvageValue,0)


return @returnValue;

end;



create function dbo.udfComputeHardwareAssetLifeSpan(@HardwareAssetDateInstalled int, @HardwareAssetEndOfLifeDate int )
        returns int
as
begin

declare @returnValue int = 0

select @returnValue = CONVERT(DECIMAL(6,2),DATEDIFF(day,@HardwareAssetDateInstalled,
@HardwareAssetEndOfLifeDate)) / 365


return @returnValue;

end;

Then write a third UDF that encapsulates your HardwareAssetAccumulatedDepreciationValue IF/THEN/CASE logic.

You ~could~ pass in the computed columns into the new udfComputeHardwareAssetAccumulatedDepreciationValue function as well.

Even if you are new , you should spend a little time struggling with this concept as its a tool in the best to avoid RBAR/CURSORS.

You can get the Northwind db from here:

https://technet.microsoft.com/en-us/library/ms143221(v=sql.105).aspx

Yes, its very old, but simple demos can be created from it.

Upvotes: 1

Zakarie Abdallah
Zakarie Abdallah

Reputation: 472

you can make both of the columns user defined Function , i was looking for it but finally got , the function can access all the columns computed ND NON Computed...

thank you

Upvotes: 0

TheGameiswar
TheGameiswar

Reputation: 28890

This error is not allowed to be used in another computed-column definition. occurs when you have used computed column to calculate another column value...

it seems you have below computed column

HardwareAssetDepreciableValue

And then you ,use the same to calculate below computed column

THEN CONVERT(DECIMAL(7,2),((HardwareAssetDepreciableValue / HardwareAssetLifeSpan)
/ 365))

you should not do like that..instead ,you should use base calculation

but wondering if anyone can suggest or help with alternatives to resolving the issue of multiple computed columns.

at present,there are no better alternatives ,one solution i could think of is a view to query the base table and use already existing computed values

Upvotes: 2

Related Questions