SQL Server中的WHEN ELSE in Case表达式

Question

我正在SQL Server中编写case表达式，我想在else块中添加硬编码值。

我想在case表达式中检查一下，如果下面的DHB输出是2.1和ABC001，那么它应该显示ABC Hospital的硬编码值。

SELECT DISTINCT
userid, 
CASE 
WHEN CHARINDEX('-', e.name) > 0 
THEN Ltrim(Rtrim(substring(e.name,0, charIndex('-', e.name)))) 
ELSE e.name 
END as DHB      
FROM
[Table1].[dbo].Event ae
JOIN
[Portal].[dbo].cUser cu ON cu.name = ae.userID
JOIN
[Portal].[dbo].UAttribute ua ON ua.cUser = cu.uniqueId
JOIN
[Portal].[dbo].[EIdentifier] ei ON ei.cUser = cu.uniqueId
JOIN
[Portal].[dbo].[EIdentifierType] e ON e.uniqueId = ei.externalIdentifierType
WHERE
(e.name LIKE '%ABC001%' OR e.name LIKE '2.1')
AND userId = 'Al5'

电流输出

userid    DHB
Al5     2.1
Al5      ABC001
Bl5     3.1
Bl5      CDE001

预期输出

userid    DHB
Al5     ABC Hospital
Bl5      EFG Hospital

我在下面试过这样的东西

CASE WHEN Ltrim(Rtrim(substring(e.name,0, charIndex('-', e.name)))) = 'ABC001' THEN 'ABC Hospital'
WHEN e.name = '2.1' THEN 'ABC Hospital'
ELSE e.name 'ABC Hospital'
END as DHB 

但它不允许我输入ELSE e.name 'ABC Hospital'。

非常感谢您的帮助！！

Answer 1

我能够解决您的问题并更新查询以满足您的需求。

SELECT DISTINCT userid,
CASE WHEN DHB in ('2.1','ABC001') THEN 'ABC Hospital'
ELSE DHB END 
FROM 
(
SELECT 
userid, 
CASE 
WHEN CHARINDEX('-', e.name) > 0 
THEN Ltrim(Rtrim(substring(e.name,0, charIndex('-', e.name)))) 
ELSE e.name 
END as DHB      
FROM
[Table1].[dbo].Event ae
JOIN
[Portal].[dbo].cUser cu ON cu.name = ae.userID
JOIN
[Portal].[dbo].UAttribute ua ON ua.cUser = cu.uniqueId
JOIN
[Portal].[dbo].[EIdentifier] ei ON ei.cUser = cu.uniqueId
JOIN
[Portal].[dbo].[EIdentifierType] e ON e.uniqueId = ei.externalIdentifierType
WHERE
(e.name LIKE '%ABC001%' OR e.name LIKE '2.1')
AND userId = 'Al5'
) as aa