CODEWITHSUNDEEP
mysqlcasenested
Charles McAnany
Charles McAnany

Reputation: 195

case within a select case in mysql

I'm getting sick of trying to hook up to MSSQL, so I'm switching over to mysql. It's slow progress. Here's my current stumper: mssql:

create function W(m varchar(255)) returns int begin

declare @e int
set @e = (select COUNT(N) from P where N = m)

declare @t int
set @t = dbo.C(m)

return case @t 
when 0 then -1 
when 1 then
    case @e when 0 then -1 else 1 end
when 2 then
    case @e when 1 then -1 when 2 then 0 when 3 then 0 when 4 then 1 end
when 3 then 
    case @e when 1 then -1 when 2 then 1 end
when 4 then 
    case @e when 1 then -1 when 2 then 0 when 3 then 1 end
end
end

I'd like to switch this to mysql. Is there a valid mysql way to:

select select case n when 0 then 1 when 1 then 2 end into var

? How about 

set var = select case n when [...] end

?

Upvotes: 12

Views: 33566

Answers (2)

John Woo
John Woo

Reputation: 263723

This will guide you in using Inline IF and CASE statements in MySQL

Snippet:

SELECT CASE num_heads
           WHEN 0 THEN 'Zombie'
           WHEN 1 THEN 'Human'
           ELSE 'Alien'
       END AS race
FROM user

or

mysql> SET @val := CASE num_heads
                       WHEN 0 THEN 'Zombie'
                       WHEN 1 THEN 'Human'
                       ELSE 'Alien'
                   END AS race;

mysql> SELECT @val;

Upvotes: 31

ruakh
ruakh

Reputation: 183341

Do you mean

SET @var := CASE n WHEN [...] END;

?

(http://dev.mysql.com/doc/refman/5.6/en/user-variables.html)

Upvotes: 1

Related Questions