SQL Server:在另一个计算列中使用的计算列结果

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!

欢迎任何帮助或建议!

3 个解决方案

#1

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

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

目前，没有更好的替代方案，我能想到的一个解决方案是查询基表并使用已有的计算值的视图

#2

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

计算列对于一个“1 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.

现在，我不喜欢下面的udfexampleudftwo - doubleudfone idea。但它可能对你有用。

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.

然后编写第三个UDF来封装您的hardwareassetaccumulationvalue IF/ Then /CASE逻辑。

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

您还可以将计算出的列传递到新的udfcomputehardwareassetaccumulationvalue函数中。

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.

即使您是新手，您也应该花一点时间来研究这个概念，因为它是避免RBAR/游标的最佳工具。

You can get the Northwind db from here:

你可以从这里得到北风db:

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

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

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

是的，它非常古老，但是可以从中创建简单的演示。

#3

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

你可以让这两个列用户定义函数，我一直在找它，但最终得到，函数可以访问所有列计算的ND，非计算的…

thank you

谢谢你！

#1