SQL 你能做一个 ELSE WHEN 吗？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/8001538/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
can you do an ELSE WHEN on a CASE
提问by Booksman
CASE WHEN P.NURSING_UNIT is not null THEN P.NURSING_UNIT ELSE '' END NURSING_UNIT
,CASE WHEN P.UNIT_CODE is not null THEN P.UNIT_CODE ELSE '' END UNIT_CODE,
CASE WHEN M.SIGN_DATE IS NOT NULL THEN 'COMPLETED' ELSE
WHEN M.SIGN_DATE IS NULL THEN 'UNCOMPLETED' AS ASSESSMENTS
error: because the sp is not compiling now. getting this error message: Msg 156, Level 15, State 1, Procedure GET_SCHEDULE_ALL_DETAIL, Line 18 Incorrect syntax near the keyword 'WHEN'. Msg 156, Level 15, State 1, Procedure GET_SCHEDULE_ALL_DETAIL, Line 25 Incorrect syntax near the keyword 'AND'.
错误：因为 sp 现在没有编译。收到此错误消息：消息 156，级别 15，状态 1，过程 GET_SCHEDULE_ALL_DETAIL，第 18 行关键字“WHEN”附近的语法不正确。消息 156，级别 15，状态 1，过程 GET_SCHEDULE_ALL_DETAIL，第 25 行关键字“AND”附近的语法不正确。
----
USE [PRO]
GO
/****** Object: StoredProcedure [dbo].[GET_SCHEDULE_ALL_DETAIL] Script Date: 11/02/2011 14:14:50 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
alter PROC [dbo].[GET_SCHEDULE_ALL_DETAIL]
@FACILITYKEY varchar(1000),
@UNITSTR VARCHAR(100),
@FromDate datetime,
@ToDate datetime
AS
BEGIN
(SELECT S.FACILITY_KEY, S.PAT_NUMBER, S.A3A_DATE_USER, M.REFERENCE_DATE ,
RTRIM(P.LAST_NAME) + CASE WHEN RTRIM(P.FIRST_NAME) <> '' THEN ', '
ELSE ''
END + RTRIM(P.FIRST_NAME) PATIENT_NAME
,CASE WHEN P.NURSING_UNIT is not null THEN P.NURSING_UNIT ELSE '' END NURSING_UNIT
,CASE WHEN P.UNIT_CODE is not null THEN P.UNIT_CODE ELSE '' END UNIT_CODE,
CASE WHEN M.SIGN_DATE IS NOT NULL THEN 'COMPLETED' ELSE
WHEN M.SIGN_DATE IS NULL THEN 'UNCOMPLETED' AS ASSESSMENTS
FROM [PC].MDS_M_SCHEDULE S INNER JOIN OPTC.MD3_M_MAST M
ON S.PAT_NUMBER=M.PAT_NUMBER
LEFT JOIN OGEN.GEN_M_PATIENT_MAST P ON S.PAT_NUMBER = P.PAT_NUMBER
WHERE S.PAT_NUMBER=M.PAT_NUMBER AND M.REFERENCE_DATE < GETDATE()
AND S.A3A_DATE_USER BETWEEN @FromDate AND @ToDate
AND S.FACILITY_KEY IN (SELECT Value FROM dbo.ListToTable(@FACILITYKEY,','))
AND ( @UNITSTR IS NULL
OR @UNITSTR = ''
OR CHARINDEX(P.UNIT_CODE, @UNITSTR)% 2 = 1 ))
UNION ALL
(SELECT * FROM (
(SELECT S.FACILITY_KEY, S.PAT_NUMBER, S.A3A_DATE_USER, M.REFERENCE_DATE ,
RTRIM(P.LAST_NAME) + CASE WHEN RTRIM(P.FIRST_NAME) <> '' THEN ', '
ELSE ''
END + RTRIM(P.FIRST_NAME) PATIENT_NAME
,CASE WHEN P.NURSING_UNIT is not null THEN P.NURSING_UNIT ELSE '' END NURSING_UNIT
,CASE WHEN P.UNIT_CODE is not null THEN P.UNIT_CODE ELSE '' END UNIT_CODE, 'LATE' AS ASSESSMENTS
FROM [PC].MDS_M_SCHEDULE S INNER JOIN OPTC.MD3_M_MAST M
ON S.PAT_NUMBER=M.PAT_NUMBER
LEFT JOIN OGEN.GEN_M_PATIENT_MAST P ON S.PAT_NUMBER = P.PAT_NUMBER
WHERE M.REFERENCE_DATE < GETDATE() AND S.A3A_DATE_USER BETWEEN @FromDate AND @ToDate
AND ( @UNITSTR IS NULL
OR @UNITSTR = ''
OR CHARINDEX(P.UNIT_CODE, @UNITSTR)% 2 = 1 )
) --Started
UNION ALL
(SELECT S.FACILITY_KEY, S.PAT_NUMBER, S.A3A_DATE_USER, NULL AS REFERENCE_DATE,
RTRIM(P.LAST_NAME) + CASE WHEN RTRIM(P.FIRST_NAME) <> '' THEN ', '
ELSE ''
END + RTRIM(P.FIRST_NAME) PATIENT_NAME
,CASE WHEN P.NURSING_UNIT is not null THEN P.NURSING_UNIT ELSE '' END NURSING_UNIT
,CASE WHEN P.UNIT_CODE is not null THEN P.UNIT_CODE ELSE '' END UNIT_CODE, 'LATE' AS ASSESSMENTS
FROM [PC].MDS_M_SCHEDULE S INNER JOIN OPTC.MD3_M_MAST M
ON S.PAT_NUMBER=M.PAT_NUMBER
LEFT JOIN OGEN.GEN_M_PATIENT_MAST P ON S.PAT_NUMBER = P.PAT_NUMBER
WHERE S.PAT_NUMBER NOT IN (SELECT M.PAT_NUMBER FROM [PC].MD3_M_MAST M)
AND S.A3A_DATE_USER < GETDATE() AND S.A3A_DATE_USER BETWEEN @FromDate AND @ToDate
AND ( @UNITSTR IS NULL
OR @UNITSTR = ''
OR CHARINDEX(P.UNIT_CODE, @UNITSTR)% 2 = 1 )) --Not Started
) LATE
WHERE FACILITY_KEY IN (SELECT Value FROM dbo.ListToTable(@FACILITYKEY,',')))
END
GO
采纳答案by onedaywhen
Standard SQL:
标准 SQL：
COALESCE(P.NURSING_UNIT, '') AS NURSING_UNIT,
COALESCE(P.UNIT_CODE, '') AS UNIT_CODE,
CASE
WHEN M.SIGN_DATE IS NULL THEN 'UNCOMPLETED'
ELSE 'COMPLETED'
END AS ASSESSMENTS
If your vendor provides a REPLACE()function:
如果您的供应商提供了一个REPLACE()功能：
COALESCE(P.NURSING_UNIT, '') AS NURSING_UNIT,
COALESCE(P.UNIT_CODE, '') AS UNIT_CODE,
COALESCE(REPLACE(M.SIGN_DATE, M.SIGN_DATE, 'COMPLETED'), 'UNCOMPLETED') AS ASSESSMENTS
回答by Dave Costa
No, ELSE is a catch-all. In your example, it's not clear why you would want to include a condition in the ELSE clause, since you've already checked the logically opposite condition in the first WHENexpression.
不，ELSE 是一个包罗万象的东西。在您的示例中，不清楚为什么要在 ELSE 子句中包含条件，因为您已经在第一个WHEN表达式中检查了逻辑相反的条件。
However, more generally, you can nest CASE expressions, which would look something like this:
但是，更一般地，您可以嵌套 CASE 表达式，它看起来像这样：
CASE
WHEN m.sign_date IS NOT NULL THEN 'COMPLETED'
ELSE
CASE WHEN m.start_date IS NOT NULL THEN 'IN PROGRESS' ELSE 'NOT STARTED' END
END
回答by Nathan Feger
I think you can just remove that else before the when, and add an end before 'as assessments'
我认为您可以在时间之前删除其他内容，并在“作为评估”之前添加结尾
coalesce(P.NURSING_UNIT , '') NURSING_UNIT, -- you can use coalesce here too
coalesce(P.UNIT_CODE, '') UNIT_CODE,
CASE
WHEN M.SIGN_DATE IS NOT NULL THEN 'COMPLETED'
WHEN M.SIGN_DATE IS NULL THEN 'UNCOMPLETED' END AS ASSESSMENTS
回答by Marc B
Change this part:
改变这部分：
CASE WHEN M.SIGN_DATE IS NOT NULL THEN 'COMPLETED' ELSE
WHEN M.SIGN_DATE IS NULL THEN 'UNCOMPLETED'
to
到
CASE WHEN M.SIGN_DATE IS NOT NULL THEN 'COMPLETED' ELSE 'UNCOMPLETED' END
There's no point in having if (true) { ...} else if (false) { ... }. Just make the else unconditional.
有没有意义if (true) { ...} else if (false) { ... }。只需将 else 设为无条件。
