I have a nested case loop in SQL where I'm checking it date is before 2016 then set status = 3, otherwise change status based on current status. I'm getting an error in the below code
when x.myDate is not null and x.myDate < Convert(datetime, '2015-12-31') then x.myStatus = '3'
when x.myStatus = 1 then '2'
when x.myStatus = 3 then '1'
when x.myStatus = 2 then '3'
Error: Incorrect Syntax near '=' on 2nd line
You cannot so this
x.myStatus = '3' in
Then allows only valid expression.
Expression is a combination of symbols and operators that the SQL Server Database Engine evaluates to obtain a single data value. Simple expressions can be a single constant, variable, column, or scalar function. Operators can be used to join two or more simple expressions into a complex expression.
Here is the correct way
SELECT myStatus = CASE WHEN x.myDate IS NOT NULL AND x.myDate < CONVERT(DATETIME, '2015-12-31') THEN '3' ELSE CASE x.myStatus WHEN 1 THEN '2' WHEN 3 THEN '1' WHEN 2 THEN '3' ELSE '' END END