(CASE WHEN action_officer.officer_name LIKE '%__' and RIGHT(action_officer.officer_name, 2) IN ('XX','XX','38') THEN RIGHT(action_officer.officer_name, 2) ELSE 'Unknown' END) as Team_Code,

(CASE (CASE WHEN action_officer.officer_name LIKE '% __' and RIGHT(action_officer.officer_name, 2) IN ('XX','XX', '38') THEN RIGHT(action_officer.officer_name, 2) ELSE 'Unknown' END) WHEN 'XX' THEN 'Test' WHEN '38' THEN 'S38 - North and West' ELSE 'Unknown' END) as Team_Name

In the Team_Name field, the SQL is supposed translate '38' as 'Unknown' when it should really says 'S38 - North and West'.

CASE
WHEN action_officer.officer_name LIKE '%[__]' and RIGHT(action_officer.officer_name, 2) IN ('XX','38') THEN RIGHT(action_officer.officer_name, 2) ELSE 'Unknown' END) as Team_Code,
CASE RIGHT(action_officer.officer_name, 2)
WHEN 'XX' THEN 'Test'
WHEN '38' THEN 'S38 - North and West'
ELSE 'Unknown' END as Team_Name