WHEN condition based on the current row value

Question

In my SELECT query, I would like to put a condition in my CASE for a value called IsActive so that if the current row's column Type is "adhoc" then set the value of IsActive to "n/a".

SELECT CASE Type
            WHEN 'scheduled' THEN 'Scheduled'
            WHEN 'adhoc' THEN 'AdHoc'
            ELSE 'Unknown'
       END AS 'Type',
       CASE IsActive 
            WHEN (Type) = 'adhoc' THEN 'n/a'
            WHEN 0 THEN 'Stopped'
            WHEN 1 THEN 'Active'
       END AS 'Status'
FROM MyTable

But I am getting this error on the line WHEN (Type) = 'adhoc' THEN 'n/a':

Incorrect syntax near '='.

How can I make a decision based on a condition on the current row?

Answer 1

Your second case is based on a single column, then you are trying to put a condition in the when, which is not allowed:

CASE IsActive 
            WHEN (Type) = 'adhoc' THEN 'n/a'
            WHEN 0 THEN 'Stopped'
            WHEN 1 THEN 'Active'
       END AS 'Status'

I think you meant to do this (use a case without a column and put the conditions in the when):

CASE 
WHEN [Type] = 'adhoc' THEN 'n/a'
WHEN IsActive = 0 THEN 'Stopped'
WHEN IsActive = 1 THEN 'Active'
END AS 'Status'