Why Can't I use sub-queries within a case statement in a calculated column

Question

I'm trying to run the following Query:

create table MyTable (
    TableIndex bigint primary key identity(1,1) not null,
    ForeignKey1 int not null,
    ForeignKey2 char(16) not null,
    fldSomeNumber float(24),
    fldScore as cast(case
            when fldSomeNumber is null or fldCSIPercentage=0 then 0
            when fldSomeNumber <= 0.09 then (select fldTenthScore from tblScores where ScorePrimaryKey=MyTable.ForeignKey2)
            when fldSomeNumber <= 0.9 then (select fld1Score from tblScores where ScorePrimaryKey=MyTable.ForeignKey2)
            ...
            else 100 end as float(24))
);

But I keep getting the following error: "Subqueries are not allowed in this context. Only scalar expressions are allowed." Is it not possible to put sub-selects within a calculated column like this?

I'm running SQL Server Express 2016.

Answer 1

You can't really do what you're asking at all with a single table, you'll want to use a view and put the computed column on the view.

So a view that would look something like this

CREATE VIEW MyView AS

SELECT
 cast(case
            when fldSomeNumber is null or fldCSIPercentage=0 then 0
            when fldSomeNumber <= 0.09 then tblScores.fldTenthScore
            when fldSomeNumber <= 0.9 then tblScores.fld1Score
            ...
            else 100 end as float(24)) AS fldScore
FROM
MyTable 
INNER JOIN tblScores 
ON tblScores.ScorePrimaryKey = MyTable.ForeignKey2

See the 2nd answer to this question:

formula for computed column based on different table's column

Answer 2

When you use CREATE TABLE, you must use column name and data type respectively, but here when you wrote fldScore column name, you insert a value instead of data type. because CASE structure returns a value and you only casted this value as a float number.

But if you meant computed column you can use only column in the current table to define a computed column not from another tables.

Answer 3

The way to solve this with a function (as recommended by mallan1121) is as follows:

IF EXISTS (
    SELECT * FROM sysobjects WHERE id = object_id('fnCalculateScore') 
    AND xtype IN ('FN', 'IF', 'TF')
)
    DROP FUNCTION fnCalculateScore
go

create function fnCalculateScore (
    @SomeNumber float(24),
    @SomeKey char(16)
)
returns float(24)
with schemabinding
as
begin
    return (select case
            when @SomeNumber is null or @SomeNumber=0 then 0
            when @SomeNumber <= 0.09 then (select fldTenthScore from dbo.tblScores where ScorePrimaryKey=@SomeKey)
            when @SomeNumber <= 0.9 then (select fld1Score from dbo.tblScores where ScorePrimaryKey=@SomeKey)
            ...
            else 100 end as float(24))
end
go

create table MyTable (
    TableIndex bigint primary key identity(1,1) not null,
    ForeignKey1 int not null,
    ForeignKey2 char(16) not null,
    fldSomeNumber float(24),
    fldScore as dbo.fnCalculateScore(fldSomeNumber, ForeignKey2)
);