How to set a calculated column using a subquery

问题:

I have a table to which I would like to add a calculated column. The query I want to set it to is more complex than a standard arithmetic operation and I am unsure how to set up the calculated column using the query. I attempted to use an ALTER TABLE statement:

    ALTER TABLE shareholder.Amount
ADD CalculatedAmount As 
(SELECT sum(Amount) FROM shareholder.TransactionInput T 
                    WHERE T.ShareClassLabel = Amount.ShareClassLabel
                    AND T.ValuationDate < Amount.NAVDate
                    GROUP BY T.ShareClassLabel)

But this results in an error: 'Subqueries are not allowed in this context. Only scalar expressions are allowed'. I know the sub-query itself works correctly having tested it on its own so it's just a matter of working out how to set the calculated column to be the result of it.

Thanks! (I am using SQL Server 2014 Management Studio)

回答1:

It is not possible to have a Computed Column with a Sub Query,

A computed column is computed from an expression that can use other columns in the same table.

So it is not possible to have A Query but you can use Expressions Like

ColumnA-ColumnB+ColumnC

Instead, you can convert it as a View and Compute The Column values there

Like this

CREATE VIEW MyComputedvIEW
AS
SELECT
  *,
  CalculatedAmount = (SELECT sum(Amount) FROM shareholder.TransactionInput T 
                    WHERE T.ShareClassLabel = Amount.ShareClassLabel
                    AND T.ValuationDate < Amount.NAVDate
                    GROUP BY T.ShareClassLabel)
FROM YourTable

回答2:

It is possible to add subquery with a little trick (UDF), see my example ([ChildCount] field):

CREATE TABLE [wp].[StorageData](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [Name] [nvarchar](200) NOT NULL,
    [StorageHid] [hierarchyid] NOT NULL,
    [StoragePath]  AS ([StorageHid].[ToString]()),
    [StorageLevel]  AS ([StorageHid].[GetLevel]()),
    [StorageParentHid]  AS ([StorageHid].[GetAncestor]((1))),
    [StorageParent]  AS ([StorageHid].[GetAncestor]((1)).ToString()),
    [ChildCount]  AS ([wp].[GetStorageDataChildItemCount]([StorageHid].[ToString]()))
)

CREATE FUNCTION [wp].[GetStorageDataChildItemCount]
(
    @storagePath NVARCHAR(4000)
)
RETURNS INT
AS
BEGIN

    DECLARE @ret INT = 0;
    SET @ret = (SELECT COUNT(ID) FROM [wp].[StorageData] R WHERE R.StorageParent = @storagePath)
    RETURN @ret;

END