Clever Geek Handbook

WHEN condition based on the current row value

In my query, SELECT

I would like to put a condition in mine CASE

for a value called IsActive, so if the current column of the Type column is "adhoc" then set 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 line?

+3


source to share


5 answers


The second is case

based on a single column, then you are trying to put the condition in 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 case

without a column and condition in when

):

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


source


Try the following:



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


source


Pedram jaan, your request would be like this:

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

Use Type in brackets, for example [Type]. This is the key word.

+1


source 


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


source


Operations on the currently calculated series

not supported by MS Sql Server. to achieve this you must mention Cte

with cteType as (
SELECT CASE Type
            WHEN 'scheduled' THEN 'Scheduled'
            WHEN 'adhoc' THEN 'AdHoc'
            ELSE 'Unknown'
       END AS 'Type'
FROM MyTable)

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

This will work

0


source






More articles:


All Articles