User_1983 - 1 month ago 5

SQL Question

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

`case`

when x.myDate is not null and x.myDate < Convert(datetime, '2015-12-31') then x.myStatus = '3'

else

case

when x.myStatus = 1 then '2'

when x.myStatus = 3 then '1'

when x.myStatus = 2 then '3'

else ''

end

end

Error: Incorrect Syntax near '=' on 2nd line

Please help

Answer

You cannot so this `x.myStatus = '3'`

in `CASE`

statement `Then`

. `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
```