User_1983 User_1983 - 1 month ago 5
SQL Question

Nested case in SQL referring different fields

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