Trying to use the condition in a case when expression as the result

I have a CASE expression and for one of the WHEN clauses, I’m selecting data from a table and checking if it exists. If it exists, I want to return one of the columns from that row in the THEN expressions

case 
    --a few when statements
    when name = 'aiden'
    THEN
        case
            when (select top 1 * from tableA a where [list of conditions]) = 1 and  (select top 1 value from tableB b where [list of conditions]) = 1
            THEN b.value -- this lines throwing "the multi-part identifier "b.value" could not be bound."
        else
            --do some existing logic
    Else
    --other logic
end as data

The issue I have though is SQL MS returns an error message under the b.value stating it cannot be bound. Is this because the actual referenced table is in a when statement and how would I be able to get around this if it’s possible?

Answer

The WHEN clause of the CASE expression has its own scope, so the subqueries you have executed there are inaccessible to the THEN clause.

Looking at it another way, the CASE expression forms part of the SELECT list, so any field referenced in it must appear in the FROM clause.

Advertisement

Answer