Getting Employee Details and Gender

Question

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.

Please [edit] your question to add a tag for your DBMS and include the error message in full. — David Buck, Feb 11 '22 at 07:53

score 0 · Answer 1 · answered Feb 11 '22 at 08:07

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;

score 0 · Answer 2 · answered Feb 11 '22 at 08:08

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.

Getting Employee Details and Gender

2 Answers2