case statement is not working in any of my sql platforms-CodePudding

I tried to execute the CASE STATEMENT on different platforms like POPSQL and MYSQL WORKBENCH.BUT I am getting same error why? I am not able to understand

select emp_id,emp_name,salary

CASE 
   when salary>90000 then 'Happy'
   when salary<80000 then 'okaish'
   ELSE 'satisfied'
END CASE 
from employee

CodePudding user response：

Change the alias from case to something like status as case is a reserved word

select emp_id,emp_name,salary,
CASE 
   when salary>90000 then 'Happy'
   when salary<80000 then 'okaish'
   ELSE 'satisfied'
END status
from employee

CodePudding user response：

I think CASE is supported in MySQL v5.6 . If you are using older version it will not accepted. You can use the following equivalent query with any MySQL version.

SELECT 
  emp_id, emp_name, salary,
  IF(salary>90000, 'Happy',
    IF(salary<80000, 'okaish', 'satisfied')
  )
FROM 
  employee

The idea is replacing CASE with IF which is supported by all MySQL versions.

CodePudding user response：

You wrote it wrong CASE statment, no need two CASE in start and end, CASE statment look like this:

CASE
    WHEN condition1 THEN result1
    WHEN condition2 THEN result2
    ....
    ELSE result
END;

and you missed a comma in select before CASE

select emp_id,emp_name,salary,
    CASE 
       when salary>90000 then 'Happy'
       when salary<80000 then 'okaish'
       ELSE 'satisfied' 
    END 
    from employee