select employees.emp_no, employees.birth_date, employees.first_name, employees.last_name,
case 
when dept_manager.emp_no IS NOT NULL THEN 'Manager' ELSE 'Employee' END AS Is_Manager
from employees
WHEN employees.gender = 'M' THEN 'Male' ELSE 'Female' END AS 'Gender' 
LEFT JOIN 
dept_manager ON  dept_manager.emp_no = employees.emp_no;

I wanted to get employees with their gender and whether manager or not. I thought I could use CASE with two conditions. On introducing the second condition I got an EOF error. Kindly help check what could be wrong with my code.

CodePudding user response：

This should work

select employees.emp_no, employees.birth_date, employees.first_name, employees.last_name,
(case when dept_manager.emp_no IS NOT NULL THEN 'Manager' 
ELSE 'Employee' END)Is_Manager
(case when employees.gender = 'M' THEN 'Male' ELSE 'Female' END)Gender 
from employees
LEFT JOIN 
dept_manager ON  dept_manager.emp_no = employees.emp_no;

CodePudding user response：

The query is syntactically wrong.

• FROM clause needs to go after all the list of columns in SELECT clause.
• When you added the second condition for a new column Gender, you also needed to write the keyword CASE before the WHEN condition.

Below is the correct syntax:

SELECT employees.emp_no, employees.birth_date, employees.first_name, employees.last_name,
CASE WHEN dept_manager.emp_no IS NOT NULL THEN 'Manager' ELSE 'Employee' END AS Is_Manager,
CASE WHEN employees.gender = 'M' THEN 'Male' ELSE 'Female' END AS Gender
FROM employees
LEFT JOIN 
dept_manager ON  dept_manager.emp_no = employees.emp_no;

Here is an answer on how to use the CASE syntax in SQL.