Defining new variable from Subquery - Only scalar expressions are allowed. (SQL Server)

Question

I'm trying to add a column [Missed 2x] to an existing table that indicates whether the Subject has had two consecutive missed diaries. The value is "Missed 2x" if the most recent 2 diaries are missed, and NULL otherwise. I wrote a select statement (within the code below) that successfully pulls the IDs who meet this criteria. When I created a User-Defined Function to cross-check the table and assign the value, it was untenable, as the function is recalculated for every ID every time that table is queried and took forever.

I attempted to assign the value via Alter Table by seeing if the ID of the existing table is in the Subset table. I figured this should save resources since the subset table is defined once, and the Alter statement simply checks if each ID is in the subset. Then I became aware that I can't do this with Alter Table as I get the error Subqueries are not allowed in this context. Only scalar expressions are allowed.

What is the most efficient alternative?

ALTER TABLE [R3008_Subjchar] add [Missed 2x] as 
    case when [Subject ID] in 
        (
        select
        [Subject ID]
        from(
                select distinct 
                [Subject ID]
                , [Questionnaire Name]
                , [Task Date]
                , [Is Completed]
                , dense_rank () over (Partition by [Subject ID], [Questionnaire Name] order by [Task Date] desc) as recency
                from dbo.R3008_Questionnaire
                ) r
        where recency <=2
        group by [Subject ID]
        having count(case when [Is Completed] = 'no' and [Questionnaire Name] = 'FLU-PRO Questionnaire' then [Is Completed] end) = 2
        )
    then 'Missed 2x' end

Sample data and desired results would help. – Gordon Linoff Nov 19 '20 at 22:43 — Gordon Linoff, Nov 19 '20 at 22:43

score 0 · Answer 1 · answered Nov 19 '20 at 22:47

You can't use a subquery in a computed column; it may only access the data that is on the current row, not other rows.

I don't think that storing such information is a good idea. Instead, you can create a view; lag() comes handy to implement the logic you want:

create view v_r3008_questionnaire
select q.*,
    case when [Is Completed] = 'no'
              and lag([Is Completed]) over(partition by [Subject ID], [Questionnaire Name] order by [Task Date]) = 'no'
        then 'Missed 2x'
    end as [Missed 2x]
from dbo.r3008_questionnaire

For each "failed" row, this check if the previous status for the same subject and questionnaire is failed as well, and sets the flag in that case.

Defining new variable from Subquery - Only scalar expressions are allowed. (SQL Server)

1 Answers1