MYSQL using concat with case [closed]

Question

SELECT CONCAT(firstname," ",lastname," ",CASE dept_id
WHEN  '1'    THEN  'ITD'
WHEN  '4'    THEN  'FMAD'
WHEN  '6'    THEN  'GCSMD'
WHEN  '7'    THEN  'SSHED'
WHEN  '10'   THEN  'GPD'
ELSE  'Z_ISDC'
END AS ayam) as staffname
FROM ost_staff

The output that I want from staffname is "John Doe ITD" but it doesn't work.

Answer 1

Try this, your CASE is not formatted correct.

SELECT 
CONCAT(firstname," ",lastname," ",
CASE WHEN dept_id = '1' THEN 'ITD' 
     WHEN dept_id = '4' THEN 'FMAD' 
     WHEN dept_id = '6' THEN 'GCSMD' 
     WHEN dept_id = '7' THEN 'SSHED' 
     WHEN dept_id = '10' THEN 'GPD' ELSE 'Z_ISDC' END) 
as staffname 
FROM ost_staff

Also, you cant name your CASE, a simple END is needed for the CASE.